Nested set analysis query

[prashantsanchet]

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.

[prashantsanchet]

Hi Team,

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

Regards,

Prashant

[shraddha_g]

Try,

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

[shraddha_g]

updated,

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

[prashantsanchet]

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 .