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: 
mskusace
Creator
Creator

Auto Calculate KPI for Max Date with updated data

I have a data set with multiple columns over multiple periods. A truncated version of the data set is below.

IDDepartmentStatusPeriod
WW-B1SalesRED2020-01
AZ-D4MarketingORANGE2019-12
OD-44OperationsRED2020-01
LR-UQSalesORANGE2019-12
AZ-D4MarketingRED2020-01
WW-B1SalesORANGE2019-12

 

I want a KPI metric to calculate the count for each status for the most RECENT period (2020-01 in this case). I will continuously add new data each month. I also want to know the number of IDs that exist in the PREVIOUS Period, but not the most RECENT period. Lastly, I want to know the number of IDs that exist in the most RECENT period, but not in the PREVIOUS period.

KPI Red - 3 (only for 2020-01 in this case, but will change when next month's data is loaded)

KPI Orange - 0 (same as above)

KPI Previous - 1 (ID LR-UQ exists in Period 2019-12, but not in Period 2020-01)

KPI Current - 1 (ID OD-44 exists in Period 2020-01, but not in Period 2019-12)

Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

Your Period is text, you need to convert it into proper Date format  for expression to work dynamically

Data:
LOAD
ID,
Department,
Status,
date(Date#(Period,'YYYY-MM')) as Period
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

 

Count({<Period={"$(=max(Period))"}>} [ID]) 

 

View solution in original post

6 Replies
mskusace
Creator
Creator
Author

I forgot to add, I would like to count based on each Status as well and then one KPI which does not depend on Status.

Count({<Period={'2020-01'}>} [ID]) gets me the total count for that Period, but it is not dynamic. I tried to use MaxString(Period), but I was receiving a "Nested Aggregation Not Allowed" error. 

Count({<Status={'RED'},Period={'2019-12'}>} [ID]) gets me the the total count for the Status = 'RED', but the Period is not dynamic still. 

Here is some pseudo code.

Count ID if MaxString(Period) and Status is 'RED'.

Count ID if MaxString(Period) and Status is 'ORANGE'.

Count ID if ID is in Previous Period, but NOT MaxString(Period).

Count ID if ID is in MaxString(Period), but NOT Previous Period.

Count ID if MaxString(Period).

 

Kushal_Chawda

Your Period is text, you need to convert it into proper Date format  for expression to work dynamically

Data:
LOAD
ID,
Department,
Status,
date(Date#(Period,'YYYY-MM')) as Period
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

 

Count({<Period={"$(=max(Period))"}>} [ID]) 

 

mskusace
Creator
Creator
Author

I have a field called ReportDate which is a timestamp value of when the report was run. If the ReportDate is "2020-01-02 00:00:00", I need the period to be the month prior so "2019-12". I tried to use Date(ReportDate, 'YYYY-MM') AS Period, but I get multiple values in my filter. 

Here are the values I have for the ReportDate.

2019-10-01 (this is just a date, not a timestamp)

2019-11-01 (this is just a date, not a timestamp)

2019-12-02 07:19:08 (this is a timestamp)

 

Period Filter

Period.PNG

 

Report Date Filter

ReportDate.PNG

Is there a way for me to get only the 'YYYY-MM' to show up like you have in your solution, but without the duplicates?

 
Kushal_Chawda

need to use floor function to define Month to individual date instead each timestamp

Date(floor(ReportDate), 'YYYY-MM') 

mskusace
Creator
Creator
Author

Great! All of it seems to be working as intended. I might have some issues with the filters, but I will have to see the best way to configure it. 

I added to your solution since I need the Period to be 1 month less than the ReportDate.

Date(floor(AddMonths(ReportDate,-1)),'YYYY-MM') AS Period

How did the floor function resolve that when I only have one date for each month? Two of them don't have timestamps, and they have duplicate values. The one with the timestamp did not duplicate.

Thank you very much!

mskusace
Creator
Creator
Author

How can I calculate the IDs that exist in the previous Period, but not the current period? Also, those that exist in the current Period, but not the previous Period?

If ID exists in max(Period) - 1 month, but NOT max(Period), count the ID

If ID exists in max(Period), but NOT max(Period) - 1 month, count the ID

I would normally just limit the data set to the previous Period and the Current Period, then do a count of each ID. If previous Period and count = 1, then it's unique to the previous period.

If current Period and count = 1, then it's unique to the current period. I'm not sure how to implement that or if there is a better way.