Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using Qlik Sense KPI Object with the below Expression
=Sum({1<[As Of Date]={"$(=MonthName(MAX([As Of Date])))"}>} Price)
Requirement is that I always would like to display the max Year with the as of date filter to change the data. There are some other filters also that can work with this but would like to ignore other filters.
I have also tried
Sum({<[As Of Date]={"$(=MonthName(MAX([As Of Date])))"}, Filter1=>} Price)
None of them works , what am I doing wrong here
First of all I think your first expression is just having an issue with the date formatting
if your As Of Date field is formatted in normal date formatting then changing your set expression to use the Date() function instead of the MonthName then I think that will fix one problem.
=Sum({1<[As Of Date] = {"$(=Date(Max([As Of Date])))"}>} Price)
This will give you the total of Price on the most recent As Of Date ignoring all other selections.
If you want to obey some selections and not others the easiest way to do it is to choose which ones you want to ignore and add them to the set expression so if you want to ignore selections on Region you would do this...
=Sum({$<[As Of Date] = {"$(=Date(Max([As Of Date])))"},Region =>}Price)
The one above obeys all selections except As Of Date and Region
If you wanted to ignore all and only obey a few then you need to get tricky
=Sum({1<[As Of Date] = {"$(=Date(Max([As Of Date])))"} Region = $::Region>}Price)
the argument with the $::Region is an Alternate State trick where you can set an expression to obey selections from specific fields from different selection states the $ gives you the default state. So the intial 1 overrides all selections then Region=$::Region forces the expression to obey the region field's selections.
I hope that helps.