Hey All,
I'm using Qlik Sense and have a measure that computes corporate earnings from Inception To Date ([Earnings ITD]) and an [Earnings Month] field. I also have a measure that computes the corporate earnings for just the Earnings Months selected ([Earnings In Period]).
What I would like to do is have a sheet in my app with a KPI and a Table on it. When a user selects a single [Earnings Month], the KPI of [Earnings ITD] will just be the Corporate Earnings up until the selected [Earnings Month] and the table will have a single row in it, i.e if a user select March 2018:
KPI:
Earnings ITD
100,000
Table:
Earnings Month Earnings In Period Earnings ITD
March 2018 10,000.00 100,000
If a user selects multiple Earning Months, I would like the KPI to be the ITD total of the most recently selected Month, i..e. if a user selects Jan 2018, Feb 2018 and March 2018:
KPI:
Earnings ITD
100,000
Table:
Earnings Month Earnings In Period Earnings ITD
Jan 2018 22,000.00 75,000.00
Feb 2018 15,000.00 90,000.00
March 2018 10,000.00 100,000.00
However, I can't find the right combination of Functions to make this work. Just doing a typical sum for Earnings ITD in the example above, i'd get
KPI:
Earnings ITD
265,000
I'd like to say something along the lines of:
IF(GETSELECTEDCOUNT([Earnings Month]) > 1, MAX([Earnings Month]), [Earnings Month]), which, when added to the Set Expression makes the KPI correct but gives me:
Table:
Earnings Month Earnings In Period Earnings ITD
Jan 2018 22,000.00 100,000.00
Feb 2018 15,000.00 100,000.00
March 2018 10,000.00 100,000.00
I've written quite a bit of DAX in a previous life and there was a function called HASONEVALUE, which evaluated the context in both the filter and the table row and would have made the value correct in both the KPI and table above. Is there something similar to that in Qlik that I just haven't found yet?
Thanks in advance!
