topic Re: Count if using set analysis in App Development

Count if using set analysis

Anonymous — Tue, 04 Sep 2018 10:32:02 GMT

Hi there Experts,

Hope someone could help me out.

I have a straight table with the following dimensions:

Dimension: Case

Measurements: Set Analysis

PSR Pickup Request Date:

Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])

RMA Online Booking Date:

Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])

# of days aging:

Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date]) - Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])

Pass/Fail:

if((Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])-Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date]))<=1,'Passed','Failed')

Now, using KPI in my chart:

> I want to count "# of days aging" that are "<=1"

> I want to count "# of days aging" that are ">1"

Thank you so much for your inputs!

Regards,

Re: Count if using set analysis

ogautier62 — Tue, 04 Sep 2018 11:47:15 GMT

Hi,

maybe you can reuse Pass/Fail:

for count of <=1 :

sum(total if(aggr((Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])-Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date]),Case))<=1,1,0))

regards

Re: Count if using set analysis

Anonymous — Tue, 04 Sep 2018 12:10:30 GMT

Thank you so much Oliver,

However, I've gotten an "Error in expression" using the same formula

Sum(Total if(aggr((Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])- Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date]),CaseNumber))<=1,1,0))

Hope we can check again what went wrong.

Thanks again!

Re: Count if using set analysis

ogautier62 — Tue, 04 Sep 2018 12:27:27 GMT

maybe parenthesis in bold :

Sum(Total if(aggr((Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])- Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])),CaseNumber))<=1,1,0))

I can't check in editor without these fields,

with error you must have an underline or a character in red to help you,

you can too double click on each left ( to see if it ends on the right good one (in yellow)

Re: Count if using set analysis

Anonymous — Tue, 04 Sep 2018 12:39:51 GMT

Still showing the same error, Olivier.

I do not know how to attached an excel file. Please bear on the Raw field shown below. Hope you can use this.

CaseNumber	CreatedDate.autoCalendar.Date	PSR_or_RMA_Transaction
01347853	8/20/2018	PSR
01354776	8/30/2018	PSR
01311826	8/17/2018	PSR
01354726	8/22/2018	PSR
01342698	8/16/2018	RMA
01354548	8/22/2018	RMA
01311826	8/17/2018	RMA
01325278	8/31/2018	RMA
01338590	8/13/2018	RMA
01325266	8/27/2018	RMA
01354726	8/22/2018	RMA
01314571	8/28/2018	RMA
01337067	8/27/2018	RMA
01354776	8/31/2018	RMA
01347853	8/21/2018	RMA
01325278	8/31/2018	PSR
01325266	8/27/2018	PSR
01314571	8/28/2018	PSR
01337067	8/24/2018	PSR
01342698	8/13/2018	PSR
01354548	8/22/2018	PSR

Thank you!

Re: Count if using set analysis

Anonymous — Tue, 04 Sep 2018 12:50:02 GMT

Hi Olivier,

I think I have spotted it.

Sum(Total if(Aggr((Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])-Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])),CaseNumber)<=1,1,0))

Thank you so much!

Re: Count if using set analysis

Anonymous — Thu, 06 Sep 2018 12:48:03 GMT

Hello Olivier,

I have a follow up inquiry, hope you could provide assistance.

I want to create a combo chart (Bars and Line), here's what I have:

Dimension: Work Week

Measures:

Height of bar

Pickup Requests: Count({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])

Booking of RMAs: sum(total if(Aggr((max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])-max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])),CaseNumber)<=1,1,0))

Height of line

SLA

sum(Total if(Aggr((Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])-Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])),CaseNumber)<=1,1,0)) /

count({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])

Here's the chart will look like

Now here is my problem:

> As you can see the RMA Booking was not broken down per week it was reflecting the totals values each week instead, do you have an idea (expressions) on how to solve my problem?

> my SLA measures will be fixed once my it was solved

RMA Booking should be broken down as follows:

WW33 - 1

WW34 - 3

WW35 - 4

I have here the sample Table and Raw data

Sample straight table output

Case	CreatedDate.autoCalendar.Week	PSR Pickup Request Date	RMA Online Booking Date	# of days aging	Pass/Fail
01325278	W35	8/31/2018	8/31/2018	0	Passed
01354776	W35	8/30/2018	8/31/2018	1	Passed
01314571	W35	8/28/2018	8/28/2018	0	Passed
01325266	W35	8/27/2018	8/27/2018	0	Passed
01337067	W34	8/24/2018	8/27/2018	3	Failed
01354726	W34	8/22/2018	8/22/2018	0	Passed
01354548	W34	8/22/2018	8/22/2018	0	Passed
01347853	W34	8/20/2018	8/21/2018	1	Passed
01311826	W33	8/17/2018	8/17/2018	0	Passed
01342698	W33	8/13/2018	8/16/2018	3	Failed
01338590	W33	-	8/13/2018	-	Failed

Sample Raw Data

Case	Date	CreatedDate.autoCalendar.Week	PSR_or_RMA_Transaction
01354776	8/30/2018	W35	PSR
01354776	8/31/2018	W35	RMA
01354726	8/22/2018	W34	PSR
01354726	8/22/2018	W34	RMA
01354548	8/22/2018	W34	RMA
01354548	8/22/2018	W34	PSR
01347853	8/20/2018	W34	PSR
01347853	8/21/2018	W34	RMA
01342698	8/16/2018	W33	RMA
01342698	8/13/2018	W33	PSR
01338590	8/13/2018	W33	RMA
01337067	8/27/2018	W35	RMA
01337067	8/24/2018	W34	PSR
01325278	8/31/2018	W35	RMA
01325278	8/31/2018	W35	PSR
01325266	8/27/2018	W35	RMA
01325266	8/27/2018	W35	PSR
01314571	8/28/2018	W35	RMA
01314571	8/28/2018	W35	PSR
01311826	8/17/2018	W33	PSR
01311826	8/17/2018	W33	RMA

Thank you so much!

Re: Count if using set analysis

ogautier62 — Thu, 06 Sep 2018 13:10:39 GMT

if you change dimensions,

you must change it too in aggr !

and add Work Week in aggr formula

Re: Count if using set analysis

Anonymous — Thu, 06 Sep 2018 13:24:07 GMT

Hi Olivier,

I have tried that as well with below formula:

Dimension: Work Week

Measures:

Height of bar

Pickup Requests: Count({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])

Height of line

SLA

sum(Total if(Aggr((Max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])-Max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])),[CreatedDate.autoCalendar.Week])<=1,1,0)) /

count({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])

Here's what I've got in my chart:

Re: Count if using set analysis

ogautier62 — Thu, 06 Sep 2018 13:27:40 GMT

you need to add, not replace !

so aggr(................, week, case)

the

Re: Count if using set analysis

Anonymous — Thu, 06 Sep 2018 13:38:59 GMT

My apology Olivier, rookie problem.

Don't know if I got the correct formula below, I wasn't able to get the figures that I want.

sum(total if(Aggr((max({<PSR_or_RMA_Transaction={'RMA'}>}[CreatedDate.autoCalendar.Date])-max({<PSR_or_RMA_Transaction={'PSR'}>}[CreatedDate.autoCalendar.Date])),[CreatedDate.autoCalendar.Week], CaseNumber)<=1,1,0))

Thank you so much for any help.

Re: Count if using set analysis

ogautier62 — Thu, 06 Sep 2018 13:52:27 GMT

I havn't any more datas to test,

what's wrong ?

maybe remove total after sum(

or write sum(total <Week>

Re: Count if using set analysis

Anonymous — Thu, 06 Sep 2018 13:56:33 GMT

I removed the Total after sum and it worked!

Thank you so much Olivier!!!