Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlik Community,
I have to create a Pivot table like the one below:
Column: Calendar Year/Month
Values: Net VAVE forcast
Rows: I want to create a dimension with 3 fields: (New),(Carryover),(YTD)
(New): Should display the values of the month of the current year
(Carryover): Should display the values of the same month of previous year
(YTD): Should display the cumulative sum of :(NEW of the said month)+(Carryover of the said month) + (YTD of the previous month)
My code in the set analysis is : =if([Implementation Date (Year)]=([Calendar Year]-1),'Carry over', 'NEW')
Can someone help how to implement the YTD in the above code?
An example of the expected result is below:
Best,
AS
1st | 2nd | 3rd | 4th | 5th | 6th | 7th | 8th | 9th | 10th | 11th | 12th | |
Actual | Actual | Actual | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | |
(single: New) | 53.113 | 58.481 | 69.420 | 69.420 | 69.420 | 69.420 | 69.420 | 69.420 | 69.420 | 69.420 | 69.420 | 69.420 |
(single: Carryover) | 154.621 | 144.970 | 169.503 | 105.242 | 89.680 | 79.505 | 52.016 | 53.897 | 37.051 | 30.488 | 12.765 | 0.000 |
(YTD) | 207.733 | 411.185 | 650.108 | 824.770 | 983.870 | ####### | ####### | ####### | ####### | ####### | ####### | ####### |
Try like below
Hope (single: New), (single: Carryover), (YTD) are Measures..
In that case, For (YTD), try with column names of remaining 2 measures like
[(single: New)]+[(single: Carryover)]+ alt(Before(YTD),0)
o.p: