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

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:

pivot table_cases per day.PNG937787 17.4 KB

This is how Id 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 months or years 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.

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:

DrWindy:

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

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

@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