Pivot: aggregation within a day on CASE ID, correctly displaying on month and year level

Hi everyone!

I want to build a pivot table with “year” as pivot, month and day as dimentions and the KPI that counts distinct CASE_IDs within the day but just simply sums them up for month or year.
So my daily KPI looks at the moment like

COUNT(DISTINCT (CASE WHEN “table1”.Activity1"=‘A’ then “table1”.“CASE_ID” ELSE NULL END))

The problem is that I want a simple sum of daily counts for the month’s and year’s result. Is there a way to aggregate the values by day in the form
sum(for each day COUNT (DISTINCT (CASE WHEN “table1”.Activity1"=‘A’ then “table1”.“CASE_ID” ELSE NULL END)) )?

Thanks!

Best,
Maria

Hi Maria,

If I understand you correctly you want to count the unique cases with activity A taking place on a on particular day, month and year were the sum is shown for each level of aggregation?

In other words, a table that looks like this:

This is how I’d set up the table:

First dimension:

YEAR(“ACTIVITY TABLE”.“EVENTTIME”)

Second dimension:

ROUND_MONTH(“ACTIVITY TABLE”.“EVENTTIME”)

Third dimension:

DAY(“ACTIVITY TABLE”.“EVENTTIME”)

Alternatively if you prefer the date written out:

ROUND_DAY(“ACTIVITY TABLE”.“EVENTTIME”)

KPI:

COUNT( DISTINCT “CASE TABLE”."_CASE_KEY")

Component filter:

FILTER “ACTIVITY TABLE”.“ACTIVITY_EN” = ‘A’

Finally, remember to click the box next to “Show KPI summary” under “Advanced options” to show the column and row sums.

Does this answer your question?

Best wishes,

Calandra

Hi Calandra,

thanks for your reply but i think I was not too good in explaining the problem. Our staff proceeds all the mails (Activity ‘Mail recieved’) within a day for each Customer_ID. So within the day we do not Count the Incoming mails but rather the distinct Customer_IDs with activity ‘Mail recieved’ to determine our “work basket”. The Formula

COUNT(DISTINCT (CASE WHEN “table1”.Activity1"=‘A’ then “table1”.“CASE_ID” ELSE NULL END))
works fine for that.

But if i use the Formula to Aggregate on the month’s or year’s Level, I will encounter a Problem because Customer_ID will be counted only once a month(year) even if the mails come every day.

Example:
Date______ Customer ID
01.08.2019___ 12345
01.08.2019 ___67890
02.08.2019 ___12345
02.08.2019 ___22222
03.08.2019 ___12345
03.08.2019 ___22222

will be translated into (Pivot year, kpi Formula above):

Date _____2019
August 19 ___3
01.08.2019 __2
02.08.2019 __2
03.08.2019 __2

Whereas the number I am looking for for the month August would be 6.

Best regards,
Maria

Maybe you could try concatenate ID with day (or day+month+year)

Your data would look like this:
01.08.2019___ 0112345
01.08.2019 ___0167890
02.08.2019 ___0212345
02.08.2019 ___0222222
03.08.2019 ___0312345
03.08.2019 ___0322222

All ID would be unique per day.

2 Likes

I think @bdziedzi made an excellent suggestion.

Since you want to aggregate by year, it would be wise to concatenate on day and month (in case the same ID appears on the same day of the month in multiple months).

The way to implement it would be to change:

to:

COUNT(DISTINCT (CASE WHEN “table1”.Activity1"=‘A’ then DAY(“table1”.“Eventtime” || MONTH(“table1”.“Eventtime” || “table1”.“CASE_ID” ELSE NULL END))

Is there anything else we can help you with?

Best wishes,

Calandra

1 Like

@bdziedzi, your r a genius! Thanks!

COUNT(DISTINCT(CASE WHEN “TABLE1”.“ACTIVITY”=‘Mail recieved’ THEN CONCAT(“Table2”.“Customer_ID”,ROUND(DAYS_BETWEEN(“Table1”.“EVENTTIME”,TODAY()))) ELSE NULL END))

Best,
Maria

You welcome :slight_smile:.
Unfortunately I’m not but had a thought when I saw your example.

Kind regards,