Single Measure For KPI and Table

dudleytmc — Tue, 27 Mar 2018 17:22:05 GMT

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!

Re: Single Measure For KPI and Table

dwforest — Tue, 27 Mar 2018 23:00:31 GMT

Set Expression is how Qlik accomplishes this. (Not sure how your IF was used in a Set Expression)

Sum({$<[Earnings Month]={"=$(=Max([Earnings Month]))"}>} [Earnings])

topic Single Measure For KPI and Table in App Development

Single Measure For KPI and Table

Re: Single Measure For KPI and Table