Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
israelcanlas
Contributor
Contributor

Count if using set analysis

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,

IC

1 Solution

Accepted Solutions
ogautier62
Specialist II
Specialist II

I havn't any more datas to test,

what's wrong ?

maybe remove total after sum(

          or write sum(total <Week> 

View solution in original post

12 Replies
ogautier62
Specialist II
Specialist II

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

israelcanlas
Contributor
Contributor
Author

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!

ogautier62
Specialist II
Specialist II

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)

israelcanlas
Contributor
Contributor
Author

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.

CaseNumberCreatedDate.autoCalendar.DatePSR_or_RMA_Transaction
013478538/20/2018PSR
013547768/30/2018PSR
013118268/17/2018PSR
013547268/22/2018PSR
013426988/16/2018RMA
013545488/22/2018RMA
013118268/17/2018RMA
013252788/31/2018RMA
013385908/13/2018RMA
013252668/27/2018RMA
013547268/22/2018RMA
013145718/28/2018RMA
013370678/27/2018RMA
013547768/31/2018RMA
013478538/21/2018RMA
013252788/31/2018PSR
013252668/27/2018PSR
013145718/28/2018PSR
013370678/24/2018PSR
013426988/13/2018PSR
013545488/22/2018PSR

Thank you!

israelcanlas
Contributor
Contributor
Author

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!

israelcanlas
Contributor
Contributor
Author

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

CaseCreatedDate.autoCalendar.WeekPSR Pickup Request DateRMA Online Booking Date# of days agingPass/Fail
01325278W358/31/20188/31/20180Passed
01354776W358/30/20188/31/20181Passed
01314571W358/28/20188/28/20180Passed
01325266W358/27/20188/27/20180Passed
01337067W348/24/20188/27/20183Failed
01354726W348/22/20188/22/20180Passed
01354548W348/22/20188/22/20180Passed
01347853W348/20/20188/21/20181Passed
01311826W338/17/20188/17/20180Passed
01342698W338/13/20188/16/20183Failed
01338590W33-8/13/2018-Failed

Sample Raw Data

   

CaseDateCreatedDate.autoCalendar.WeekPSR_or_RMA_Transaction
013547768/30/2018W35PSR
013547768/31/2018W35RMA
013547268/22/2018W34PSR
013547268/22/2018W34RMA
013545488/22/2018W34RMA
013545488/22/2018W34PSR
013478538/20/2018W34PSR
013478538/21/2018W34RMA
013426988/16/2018W33RMA
013426988/13/2018W33PSR
013385908/13/2018W33RMA
013370678/27/2018W35RMA
013370678/24/2018W34PSR
013252788/31/2018W35RMA
013252788/31/2018W35PSR
013252668/27/2018W35RMA
013252668/27/2018W35PSR
013145718/28/2018W35RMA
013145718/28/2018W35PSR
013118268/17/2018W33PSR
013118268/17/2018W33RMA

Thank you so much!

IC

ogautier62
Specialist II
Specialist II

if you change dimensions,

you must change it too in aggr !

and add Work Week in aggr formula

israelcanlas
Contributor
Contributor
Author

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

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])),[CreatedDate.autoCalendar.Week])<=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])),[CreatedDate.autoCalendar.Week])<=1,1,0)) /

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

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

ogautier62
Specialist II
Specialist II

you need to add, not replace !

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

the