topic Re: Auto Calculate KPI for Max Date with updated data in App Development

Auto Calculate KPI for Max Date with updated data

mskusace — Sat, 16 Nov 2024 03:40:54 GMT

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

ID	Department	Status	Period
WW-B1	Sales	RED	2020-01
AZ-D4	Marketing	ORANGE	2019-12
OD-44	Operations	RED	2020-01
LR-UQ	Sales	ORANGE	2019-12
AZ-D4	Marketing	RED	2020-01
WW-B1	Sales	ORANGE	2019-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)

Re: Auto Calculate KPI for Max Date with updated data

mskusace — Fri, 03 Jan 2020 18:55:11 GMT

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

Re: Auto Calculate KPI for Max Date with updated data

Kushal_Chawda — Fri, 03 Jan 2020 19:00:36 GMT

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

Re: Auto Calculate KPI for Max Date with updated data

mskusace — Fri, 03 Jan 2020 19:27:43 GMT

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

Report Date Filter

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?

Re: Auto Calculate KPI for Max Date with updated data

Kushal_Chawda — Fri, 03 Jan 2020 19:33:02 GMT

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

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

Re: Auto Calculate KPI for Max Date with updated data

mskusace — Fri, 03 Jan 2020 19:49:38 GMT

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!

Re: Auto Calculate KPI for Max Date with updated data

mskusace — Fri, 03 Jan 2020 19:57:59 GMT

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.