Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
I should have said that I'm trying to achieve this using an expressions.
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
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)
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, ' , ')
@@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.
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!
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?
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)
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)