sum of dimensions & cummulative sum in pivot

[#1]

Hi All,

As am newbie to Qlikview, Please help me to add 3 dimensions in the Pivot table. I have attached my schema.

My Pivot will look like the table below:

MMM/YY	Dev	Prd	ATS	Total
Oct-11	2	1	3	6
Nov-11	0	1	0	1
Dec-11	1	0	0	1
Jan-11	0	0	1	1
Feb-11	1	1	1	3

1. I want to write expression to get the "Total" column populated with the Total of Dev,Prd & ATS for every month(eg: Oct

-11 (2+1+3=6))

I tried using Sum(Total<dim1,dim2>) but its not working.   Not sure how the expression works

2. Also i want to take cummulative total, wherein am trying to get Year-To-Date(YTD)

value i.e if my data(as above) is from Oct-2011 to Mar-2014, then i want to get total of 12 months for every dimension(Dev,Prd & ATS) eg: for Sep-12 the YTD (for dev) will be sum of  Dev from Oct-11 to Sep-12 similarly for Prd & ATS. So i wish to create another table with this YTD value wherein, my data value would start from Sep-12. Perhaps it should look like:

MMM/YY	Dev	Prd	ATS	Total
Sep-12	2	19	1	22
Oct-12	4	19	1	24
Nov-12	5	20	1	26

Please help me out.. Thanks in Advance!!!

[its_anandrjs · Accepted solution]

For total you can write

Sum(Dev) + Sum(Prd) + Sum(ATS) for total

[MK_QSL]

What is DEV, Prd, ATS. If they are values of your field...you can write as

SUM({<YourFieldName = {'DEV','Prd','ATS'}>}Qty)

Where SUM could be COUNT and QTY could be SALES accordingly to your data model.

If they are not the values of your any field... you can use

Column(1) + Column(2) + Column(3) as a static expression or

SUM(Dev) + SUM(Prd) + SUM(ATS) as a dynamic expression

Considering that your TOTAL column name is TOTAL

You can have cumulative total as

RangeSum(Above(TOTAL,0,RowNo()))

[#4]

Thanks Dude! It worked out

[#5]

Hi Manish,

My first querry has been solves when i used the Dynamic expression Thanks!

But,Sorry the cummulative Sum is not working

I will elobarate on the same:

I want to get sum of Dev,Prd,ATS(i.e. My Dimensions Rel-Dev,Rel-Prd,Rel-ATS) , 12 months value and populate it into another table. I have put it in excel and explain.. pls let me know if we can solve this

[MK_QSL]

For 12 Months, you can use

RangeSum(Above(YourExpressionofATS,0,12))

Something like below

RangeSum(Above(SUM(Amount),0,12))

[#7]

Nope

Its not working. If you see below, My data i.e Rel-ATS is from Oct-11, so my YTD value should start from Sep-12 (Its the 12th month). So if i give the expression in my pivot, my pivot table should start from Sep-12,not Oct-11. Moreover, the sum value (Column YTD-ATS)itself is wrong

Please give some alternate solution