1 Reply Latest reply: Mar 27, 2018 7:00 PM by David Forest RSS

    Single Measure For KPI and Table

    Thomas Dudley

      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!