Hello friends, How can I do annual totals for two columns but only monthly totals for other columns?

[jblomqvist]

Hi all,

I have to create something like the following in a Pivot Table:

Company	Field	YearMonth	Assets	Equity	MAT BIT	MAT BITA
Coke	Food	2015-01	100,000	250,000	5,000	10,000
Coke	Food	2015-02	120,000	300,000	5,000	10,000
Coke	Food	2015-03	87,000	56,000	5,000	10,000
Pepsi	Food	2014-12	95,000	85,000	7,000	7,000

Dimensions = Company, Field, YearMonth, Type (this is the field that holds Asset, Equity, BIT, and BITA headings you see above)

Expression = e.g. Sum(Sales).

You can see in the above examples that values for Assets and Equity change for each YearMonth. However the value in MAT BIT and MAT BITA stays the same for every row for each company.

How can I get my Sum(Sales) expression to do this where MAT BIT AND MAT BITA shows results only for the last 12 months added up together, while fields like Assets and Equity change per month?

I am not sure how to do this in QlikView so please share if you know how to do this

[Gysbert_Wassenaar]

If you want rolling-12 month sums you can try one of the approaches explained in this document: Calculating rolling n-period totals, averages or other aggregations

[santhoo_san]

Hi you can create a running month ID and use as below

Sum({<MonthID ={"<=$(=max(MonthID))>=$(=max(MonthID)-11)"}>}MAT BIT)

for asset and liability you can simply use sum, as you are using YearMonth as a dimension, it will automatically takes the relevant month's value