topic Re: Nested set analysis query in App Development

Nested set analysis query

prashantsanchet — Tue, 11 Sep 2018 15:54:46 GMT

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.

Re: Nested set analysis query

prashantsanchet — Wed, 12 Sep 2018 08:35:56 GMT

Hi Team,

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

Regards,

Prashant

Re: Nested set analysis query

shraddha_g — Wed, 12 Sep 2018 08:51:59 GMT

Try,

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

Re: Nested set analysis query

shraddha_g — Wed, 12 Sep 2018 08:53:16 GMT

updated,

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

Re: Nested set analysis query

prashantsanchet — Wed, 12 Sep 2018 09:16:44 GMT

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 .