topic Re: Dynamic KPI in App Development

Dynamic KPI

Peterka — Sat, 16 Nov 2024 18:07:08 GMT

Hello all,

please advice the most appropriate solution in Qlik Sense to following:

I have a source table:

Country	Period	Value
us	01/04/2020	2623
us	01/05/2020	2585
us	01/06/2020	2596
us	01/07/2020	2606
xa	01/05/2020	3289
xa	01/06/2020	3281

I need to report on dynamic KPI (e.g. SUM of Value).

Without filter I want to show the actual value for the last common period (01/06/2020 in this example).

With Country filter, I need to show value for last respective period (in case of US it will be 01/07/2020).

Second challenge is how to detect those common periods to show not only last common value, but history progress as well.

Any idea, please??
Thanks!

Re: Dynamic KPI

Peterka — Fri, 11 Sep 2020 13:35:37 GMT

I should have said that I'm trying to achieve this using an expressions.

Re: Dynamic KPI

tresB — Fri, 11 Sep 2020 14:07:00 GMT

For your first challenge, try:

=Sum({<Period={'$(=Date(Min(Aggr(Max(Period), Country))))'}>}Value)

For second challenge, I didn't get it properly what exact output you need

Re: Dynamic KPI

tresB — Fri, 11 Sep 2020 14:34:42 GMT

@Peterka

The above expression might not work when there are cases when some dates are not there in all the country transactions. I.e. - when it is not guaranteed that min of all maximums would be available in all countries, the above expression might fail. To deal with such scenario, try:

=Sum({<Period={'$(=(Date(Max({<Period={"=Count(total Distinct Country)=Count(Distinct Country)"}>}Period))))'}>}Value)

Re: Dynamic KPI

tresB — Fri, 11 Sep 2020 14:42:24 GMT

And yes, for second challenge if you are looking for all dates common in all countries in PKI object, try:

=Concat(DISTINCT {<Period={"=Count(total Distinct Country)=Count(Distinct Country)"}>}Period, ' , ')

Re: Dynamic KPI

Peterka — Fri, 11 Sep 2020 14:54:42 GMT

@@Second challenge,

in example case it would be

01/05/2020
01/06/2020.
I'd like to use the expression as a dimension for a chart.

Re: Dynamic KPI

Peterka — Fri, 18 Sep 2020 08:44:58 GMT

I have a follow-up questions to your great answer above.

I need calculate Global KPI of Value2 for all countries, but I need to take the first period, where Value2 is not zero for all countries (see below table).

How should I extend the conditions of se expressions?

Country	Period	Value1	Value2
us	01/04/2020	2623	0
us	01/05/2020	2585	120
us	01/06/2020	2596	32
us	01/07/2020	2606	499
xa	01/05/2020	3289	0
xa	01/06/2020	3281	750

Thanks a ton!

Re: Dynamic KPI

tresB — Fri, 18 Sep 2020 10:27:09 GMT

@Peterka

Is it in addition to the above (i.e. - 'latest common date' + where Value2 is >0), or it's a new requirement? What is your expected output?

Re: Dynamic KPI

Peterka — Fri, 18 Sep 2020 10:52:03 GMT

The expected output is:
KPI from Value1 = 3289 + 2585 (for period 01/05/2020)
KPI from Value2 = 32 + 750 (for period 01/06/2020 because that is the first one having non-zero value)

Re: Dynamic KPI

tresB — Fri, 18 Sep 2020 12:13:18 GMT

@Peterka

KPI from Value1:

=Sum({<Period={'$(=(Date(Min({<Period={"=Count(total Distinct Country)=Count(Distinct Country)"}>}Period))))'}>}Value1)

KPI from Value2:

=Sum({<Period={'$(=(Date(Min({<Period={"=Count(total Distinct {<Value2={[>0]}>} Country)=Count(Distinct {<Value2={[>0]}>} Country)"}>}Period))))'}>}Value2)