Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to filter the data in my bar chart to only show the data with the date earlier than a date that is set by a user input variable.
I've tried using the following formula but it just gives me 0 for everything and does not show me the desired data. Is there something wrong with my set analysis expression for the date portion?
sum({$<[Group]={'7401'},[Shop Date]={'<($(vSelectedOPMthDate))'}>}[Hr Clocked])
The "Group" portion of the set analysis is working fine, when I remove the [Shop Date]={'<($(vSelectedOPMthDate))'} from the expression the plot appears again. It just does not work for the Shop Date portion.
Thank you!
Hi all,
Just wanted to update that I've finally solved it!!!!! So this was what was needed for the function to work. Read in another forum thread that we'd need another "$(=" for a function to be calculated.
sum({$<[Group]={'7401'}, [Shop Date]={"<$(=$(vSelectedOPMthDate))"}>}[Hr Clocked])
Thank you for helping me to think through and troubleshoot this!
You'll need double quotes for < comparison ..
sum({$<[Group]={'7401'},[Shop Date]={"<($(vSelectedOPMthDate))"}>}[Hr Clocked])
Thank you for your advice!
I just tried it though and it still did not work. The plot completely disappeared even if I changed the single quote to double quotes.
Any other possibility of what I'm doing wrong?
try this:
sum({$<[Group]={'7401'},[Shop Date]={"<$(vSelectedOPMthDate)"}>}[Hr Clocked])
(MonthEnd(AddMonths(today(2),$(vSelectedOutputMonth))))
where $(vSelectedOutputMonth) is either 0,1,2,3,etc as input by the user. Its a input variable that's supposed to represent number of months from this month.
When I display $(vSelectedOPMthDtae) it would show as 31/3/2020 if $(vSelectedOutputMonth) =0, and 30/4/2020, if $(vSelectedOutputMonth)=1, and so on.
Thanks Lisa_P, but still did not work:(
Could it be due to my [Shop Date] being in numeric form when I display it, versus my $(vSelectedOPMthDate) being in DD/M/YYYY?
tried sum({$<[Group]={'7401'},[Shop Date]={"<date#($(vSelectedOPMthDate))"}>}[Hr Clocked]), but it did not work either.
It could be that your vSelectedOutputMonth is intepreted as a double fraction 31/3/2020 = 0.00516.
Try adjusting your expression to the following (assuming DD/MM/YYYY is you default date format in the application).
=sum({$<
[Group]={'7401'},
[Shop Date]={"<'$(vSelectedOPMthDate)'"}
>}[Hr Clocked])
really appreciate your help and quick response!
unfortunately it still does not work. Once I add in the portion to do with the [Shop Date] in the set analysis, the entire data set goes missing for that Group. 😞
can you change your load script to format the [Shop Date] by loading this way:
Load Date([Shop Date]) as [Shop Date],
.....
It does seem a mismatch of data format