Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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 - Master III
Partner - Master III

Try,

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

shraddha_g
Partner - Master III
Partner - Master III

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 .