If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
I'm having some difficulty with summing some rows, in order to create a running 12 month total field. Because of the nature of the data, I have to create the result as a dimension. I want to show the total 12 months combined, for any given month.
The data is shown like so, with my own added Expected Total column.
I have tried a few different methods, such as Peek & Above functions, with the main problem being when the year ends, and only the months within that year are included in the total, with the previous year not included. So I'm not sure if it's a sorting issue or not?
Anybody able to point me in the correct direction?
Thanks in advance.
there are multiple CONTRACTs per PERIOD. Maybe that is where the issue is?
When I use Rohan's code, I am getting a cumulative total for the entire data set, as opposed to just the specific total per contract per period.
Yeah, I think what Rohan's code is doing is taking the previous 12 month's SALES, and summing that. It's not taking into consideration that it should only be for the particular CONTRACT, within that 12 month rolling period.
Change the end of the code to something like;
Temp : load date(addmonths($(min),'$(i)')) as PDate, Contract, sum(Sales) as ccsales Resident Data where date(PDate)<=date(addmonths('$(min)',$(i))) and date(PDate)>=date(addmonths(addmonths('$(min)',$(i)),-11)) GROUP BY date(addmonths($(min),'$(i)')), Contract; Next i; LEFT JOIN (Data) Load * resident Temp; drop table Temp;
This is exactly it. Thank you very much for your help, both Chris & Rohan.
I was about to write the same thing 😁, Only thing I would suggest is don't join the table in the end because then there would be multiple rows for ccSales & the sum(ccSales) may go wrong. Do check once before the join part.