Qlik Community

App Development

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

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
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)

View solution in original post

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)