# App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
cancel
Showing results for
Did you mean:  Contributor II

## Dynamic KPI

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.

Thanks!

Labels (3)

• ### Periods

1 Solution

Accepted Solutions  MVP

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)

9 Replies  Contributor II
Author

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

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  MVP

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)  MVP

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, ' , ')  Contributor II
Author

@@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.  Contributor II
Author

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!  MVP

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?  Contributor II
Author

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)  MVP

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) 