Rolling 3 months Sales for each Month Selected foe each ID

[Ramsingh]

Hi Qlik experts ,

I'm aware this question is most common and there are solutions for it but i'm still struggling as I'm quite new here .

PFA Data source and qvw

I'm trying rolling 3 months for each ID and Month :

I have used "AsofTable"  logic in my model ( hope its right , do correct me if  I'm wrong )

Requirement : For each ID selected and each Month Selected - 

1) Pervious 3 month rolling Sales 2) Pervious 2 months + current Month Selected sales ( total 3 rolling months sales ) . PFB sample pic

Sample example for output

Hope i'm clear . Do help me . Any thing is fine doing in script or front end .

Also please note without any selection , for each ID for each month in Table box / Straight Table / Pivot Table -it should show 1) Pervious Rolling 3 month sales 2) including current Month pervious 2 months rolling sales .

Please do the needful in qvw attached so that i can refer please  

Advance Thanks ,

Ram

[Kushal_Chawda]

@Ramsingh  see the attached. AsOfMonth will be your Month for selections. 

[Ramsingh]

Hi Kush ,

Thanks it seems working but when i'm checking when i added " As of Month" as Dimension and when i checked for ID1 , it has 7 rows ( including for June and July Month ) for which there is no data for June and July for ID 1 where has it has till May month only ( 5 row count ) . or is it ur using different data source by adding extra months ?

Also wanted to understand in my real data i need rolling 36 months so i can change "  while IternNo()<=36 ;  " right ?

[Kushal_Chawda · Accepted solution]

@Ramsingh as it is AsOfMonth, In Jul  it is considering , Jul,Jun and May  for Rolling 3 and Same for Rolling2. That is why for ID 1 it is having data for Jul & Jun. AsOfTable is not created on ID, it is on Month. If you don't want to show Months for that ID then I would suggest to go with front end solution.  Remove the AsOfTable logic. I have kept it to compare front end solution with AsOfTable logic. see the attached