Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Peterka
Contributor II
Contributor II

Dynamic KPI

Hello all,

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

I have a source table:

CountryPeriodValue
us01/04/20202623
us01/05/20202585
us01/06/20202596
us01/07/20202606
xa01/05/20203289
xa01/06/20203281

 

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!

Labels (3)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

View solution in original post

9 Replies
Peterka
Contributor II
Contributor II
Author

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

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

tresesco
MVP
MVP

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

tresesco
MVP
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, ' , ')

Peterka
Contributor II
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.

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

CountryPeriodValue1Value2
us01/04/202026230
us01/05/20202585120
us01/06/2020259632
us01/07/20202606499
xa01/05/202032890
xa01/06/20203281750

 

Thanks a ton!

tresesco
MVP
MVP

@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?

Peterka
Contributor II
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)

tresesco
MVP
MVP

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