Hi, I'm running into an issue with set analysis that I'm not quite understanding. I have a Sense application that has several sheets, on the first one I want to display several KPIs that should not change when other selections are made. In the expression below I'm counting orders for the Chemistry Department that have a week starting value one week prior to the maximum week.
Expression
Count({1<Department_Name = {'CHEMISTRY'},WeekStart = {"$(=Date(Max(WeekStart)-7,'M/D/YYYY hh:mm:ss TT'))"}>}[Order#])
It works fine if no selections are made but if someone selects a WeekStart value then the expression is calculating the prior week from that value. I'm sure there is something I'm not quite understanding in the dollar sign expansion that is happening but I thought that having "{1" at the beginning would ignore all selections.
I also tried doing this with a variable but achieved the same result. That one I sort of understand because I believe the variable is being evaluated at the time of the expansion. Based on some other posts I tried putting an "=" in front to create the value first but although it lets me save the variable it evaluates to nothing.
Variable Definition:
Date(max(WeekStart)-7, 'M/D/YYYY hh:mm:ss TT')
Expression:
Count({1<Department_Name = {'CHEMISTRY'},WeekStart = {"$(=$(vPriorWeek))"}>}[Order#])
Thanks in advance for any help.
