Hi everyone, Iโm having trouble with the right expression on a slice to reflect the data from the parameters selected on an interactive Dashboard. In the image below โFiltersโ is based on the Parameters table and Quotes is based on a slice with this expression. The top 3 expressions respond perfectly but when I add Start/End time it displays the blank screen. Whatโs the best expression for the Start/End Dates?
Any help would be outstanding! Thanks
AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),
CONTAINS([Quote Date],[Quote Date]>ANY(Parameters[Start Date])),
CONTAINS([Quote Date],[Quote Date]<ANY(Parameters[End Date]))
)
Solved! Go to Solution.
Does the following expression help?
AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),
[Quote Date]>ANY(Parameters[Start Date]),
[Quote Date]<ANY(Parameters[End Date])
)
Does the following expression help?
AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),
[Quote Date]>ANY(Parameters[Start Date]),
[Quote Date]<ANY(Parameters[End Date])
)
That was it! A million thanks Suvrutt for unblocking my brain
Hello, I am wondering how did you make the filter option in the dashboard?? When i look at your picture the filter view on the left. thank you
Hello, this was created with a table I called Parameters set solely for the purpose of capturing the userโs filtering options and contains one row per user email. Youโd create a Quick Edit Detail View to display the Parameter options on the left.
For the resulting data, youโll create a slice and more than likely a table view based on the slice. The slice will contain the expression above to reference the data from the Parameters table.
AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),
CONTAINS([Quote Date],[Quote Date]>ANY(Parameters[Start Date])),
CONTAINS([Quote Date],[Quote Date]<ANY(Parameters[End Date]))
)
Hope this helps to get things started for you
the formula is not working. At all choices i only get the unique ID from my main sales table.
AND(
CONTAINS([Client],ANY(Filter[Client])),
CONTAINS([Attribuรฉ ร :],ANY(Filter[Attribuรฉ ร :])),
CONTAINS([Type],ANY(Filter[Type])),
[Date dโinstallation]>ANY(Filter[Date Debut]),
[Date dโinstallation]<ANY(Filter[Date Fin])
)
ALL these statements are true:
โฆ1: (The value of column โClientโ) contains the text value (One randomly chosen value from this list (
โฆFILTER[Client]))
โฆ2: (The value of column โAttribuรฉ ร :โ) contains the text value (One randomly chosen value from this list (
โฆFILTER[Attribuรฉ ร :]))
โฆ3: (The value of column โTypeโ) contains the text value (One randomly chosen value from this list (
โฆFILTER[Type]))
โฆ4: (The value of column โDate dโinstallationโ) is greater than (One randomly chosen value from this list (
โฆFILTER[Date Debut]))
โฆ5: (The value of column โDate dโinstallationโ) is less than (One randomly chosen value from this list (
โฆFILTER[Date Fin]))
even when nothing is selected I do not see anything in the sliced table. It is always blank
i am not sure if i tagged you in my last post
ok so i spend a lot of time on this today and i figured it out. It is working but if there is no data in the field i do not get any results. meaning if filter is all empty instead of seeing all i have no data.
Now i have a problem, one of my filter parameters is an enum column and i get this error:
Table slice Filter transactions uses column โRelated Filters By Typeโ that does not exist in root table Transactions. Removing this column.
It is working for clients since it is a direct reference to the transaction table but since my โtypeโ column is an enum it is not workingโฆ anyway to get around this??
It is enum because based on the input there is a second question appearing and i can not undo this.
@Steve Can you help me?
Please post a new topic and describe your problem in detail there, with appropriate screenshots. I find it confusing trying to differentiate your problem from the original poster of this topic,
Apologies Andrian as I had a birthday weekend and was out for the weekend. Here are a few notes:
โok so i spend a lot of time on this today and i figured it out. It is working but if there is no data in the field i do not get any results. meaning if filter is all empty instead of seeing all i have no data.โ
This is correct - I usually set a few defaults in the filter options for visual appeal
โTable slice Filter transactions uses column โRelated Filters By Typeโ that does not exist in root table Transactions. Removing this column.โ
It sounds like โRelated Filters by Typeโ does not exist in the Transactions" table? Can you send a screenshot of the slice filter with complete expression as well as a screenshot of the fields in your โparametersโ table? In the meantime, please double check the slice/table associated with 'Related Filters By Typeโ to ensure the enum field exists in the associated slice/table.
I think i got everything you asked for. The enum is indeed working maybe i didnโt associated it the right wayโฆ
look if i associete the type colum in my filter table to the type tab i get this in my filter view
Letโs confirm the data being stored in the Transactions table for Type. Is it the key for Type? Letโs ensure the following represents the data being stored in the table.
I am not sure what you mean. In my Type table the key is the row number and in my transaction table I have a uniqueid as key.
this is how my type sheet was build:
Can you show how Type is stored in the Transactions table?
Oh I see, there are no records yet in the transactions table. You might want to input a few records so you can test the filtering.
For Types: I recommend a unique value (key) for each record of the Types table - Row Number can unfortunately cause issues if a record is deleted as row references the physical position of the record and does not reference the specific record.
Once you have a unique value for each unique record in Types then for the Enum youโll want to use Types[Unique Value]. We need to pull the data referenced in the transactions so when a transaction is entered, the key of Types is stored in the transactions table.
I hope this makes sense.
unique values can i use 1 to 258? or it has to be complicated?
1 thru 258 is fine. When someone enters a new Type, theyโll just need to know 259 is the next number in the sequence. You can always use UNIQUEID() and you wonโt have to worry about inputting a unique value.
the types were created directly in the google sheet they will only change if we add a new code or if we remove one and that is so rare that i do it manually.
is there a more efficient way to build an enum list? In this app you have to select the type (enum) after the service (enum) and then the billable codes (enumlist). Obviously based on the first choises the others change.
where exactly am i supposed to put the formula Type[Unique Value]?
What is the name of the new unique value field you created for Types? This new field should be marked as the key for the Types table. Youโll want to replace the โTYPEโ in this formula with the following:
Type[New Field Name]
i want to say thank you!! for not letting me down i really appreciate your time!!
here is the new column:
Youโre welcome!
Ok use: Type[IDType] and please make sure the field is set to the tableโs key.
Once youโre done with that, youโll want to key in a few test records in your Transactions table so we can see if it worked.
but where exactly do i need to put this formula?
in the filter table or in the type table or transaction table?
Youโll want to replace the โTYPEโ in this formula with the following:
Type[IDType]
Type[New Field Name]
but if i do that in my transaction table under the type i no longer get my text option i only see the uniqueid from the type table the numbers 1 to 258
In the Types table set the text youโd like to see to โLabelโ
I think i am about to cryโฆ
No please - thereโs no crying in AppSheet developing
Did it work?!!
If no, letโs jump online. We can do a join.me or something
Firs of all i want to say a big thank you to @Daisy_Ramirez. Without your idea we would of never got the solution that is so simple.
Solution: when you want to reference to a filter from an enum list create a new tab in your sheet with the filter metrics from your enum list (for me was the uniques from the type column) and link the filter to that new tab
User | Count |
---|---|
43 | |
30 | |
24 | |
23 | |
13 |