Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
prashantsanchet
Creator
Creator

Nested set analysis query

Hi team,

I have data like following

ID, Creation Date,Business Date, Score

1,10/1/2018,10/1/2018,10

1,10/1/2018,10/2/2018,20

1,10/1/2018,10/3/2018,30

2,10/1/2018,10/1/2018,50

2,10/1/2018,10/2/2018,600

2,10/1/2018,10/3/2018,50


so for each business date score will be generated.

Requirements :Want to show MTD value for  score


filter :  Creation Date : Always one selected date


Example and expected output


For ex : i want MTD as of 10/2/2018 ( selected creation date)


Expected result :


Step 1: Get the all rows for creation date  between 10/1/2018 and 10/2/2018


ID, Creation Date,Business Date, Score

1,10/1/2018,10/1/2018,10

1,10/1/2018,10/2/2018,20

2,10/1/2018,10/1/2018,50

2,10/1/2018,10/2/2018,600

Step 2 : Once we receive output i want to get record where creation date = Business Date when we are selecting single creation date


1,10/1/2018,10/1/2018,10

2,10/1/2018,10/1/2018,50


Step 3: sum(score) for above rows.


Howe this can be achieved though set analysis .

I want to show total = 60 in KPI in above case.

Request you to please let me know your thoughts.

4 Replies
prashantsanchet
Creator
Creator
Author

Hi Team,

Could you please help how to write nested set analysis for same.

Regards,

Prashant

shraddha_g
Partner
Partner

Try,

Sum({<[Business Date] = {"$(=max([Creation Date]))"}>}Score)

shraddha_g
Partner
Partner

updated,

Sum({<[Business Date] = {"$(=max([Creation Date]))"}, [Creation Date]=>}Score)

prashantsanchet
Creator
Creator
Author

Hi Shraddha,

Thanks for your reply but i am not getting expected output with above query as i need a MTD value given one creation date selected.

Request to Please help me .