Cumulative Total Calculation Month by Month

[Gopikrishna1]

Hi All,

I have to calculate cumulate Total month by month. can you please check the below attached screenshot and I want to add Jan-21 total to Feb-21 Total; For march, I have to add Jan-21 and Feb-21 data like I need to calculate month on month, can you please guide me on how to achieve this requirement.

Product Name	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21
Product A	100	200	300	400	500	600	700
Product B	110	210	310	410	510	610	710
Product C	120	220	320	420	520	620	720
Product D	130	230	330	430	530	630	730
Product E	140	240	340	440	540	640	740
Grand Total	600	1700	3300	5400	8000	11100	14700
Formule	Total sum of all Products	Jan-21GrandTotal+ Total sum of Feb-21 Products	Feb-21GrandTotal+ Total sum of Mar-21 Products	Mar-21GrandTotal+ Total sum of Apr-21 Products	Apr-21GrandTotal+ Total sum of May-21 Products	May-21GrandTotal+ Total sum of Jun-21 Products	Jul-21GrandTotal+ Total sum of Jun-21 Products

[Gopikrishna1]

Hi all,

Anyone please check my issue and guide me how to achive my senario

[Anil_Babu_Samineni]

Perhaps this? PFA

Pick(Dim, RangeSum(Before(Sum(Data), 0, RowNo(TOTAL))), Sum(Data))

[Kushal_Chawda]

@Gopikrishna1  No need to create inline dimension. Follow below steps

1) Create Pivot table 

2) In Dimension, Add Product Type in Row, and Month in Column

3) Enable Total for the Dimension Product Type from Dimension Properties

4) Write below Measure

=if(Dimensionality()=0, rangesum(before(total sum(Value)),sum(Value)), sum(Value))

[Daniel29195]

@Gopikrishna1 
hello, im not sure i did undesrtand your request correctly, but from what i undesrtood ,maybe somthing like this would help , rangesum(before(sum([# Amount]),0,2))

[robert99]

If you want a robust solution (allows drill downs by say Month or MonthYear) using a Pivot table say. Or MonthYear  as a dimension in a table

Use AsOfMonth (if you drill down to one month it will still give the correct totals whereas rangesum etc doesn't)

The As-Of Table - Qlik Community - 1466130

or

In your example above you could have 12 measures one for each month (using a table) where ProductName is the only dimension and the Months are measures. And use set analysis so you select a year and it gives the accumulated Month by Month total

I have done this where one Year is selected. So 12 expressions are set up to give the accumulated monthly totals as required 

PLActMth is a master Item measure

An example for YTD for 1st 6 months. The year is 

//Label for expression >> 'June ' & Year >> Gives June 2021 if Year is filtered to 2021 

{< Month {Jan,Feb,Mar,Apr,May,Jun}, Month = >}
PLActMth