topic Re: Pivot table with Variance in QlikView

Pivot table with Variance

Tue, 18 Mar 2014 17:16:03 GMT

Hi all,

I have a data like...

Branch	Month	Sale	Qty
1	Jan-14	5000	40
1	Feb-14	8000	60
1	Mar-14	7000	35
2	Jan-14	4500	20
2	Feb-14	6000	65
2	Mar-14	8700	80

and I want Pivot Table like

	Sale				Qty
Branch	Jan-14	Feb-14	Mar-14	Variance	Jan-14	Feb-14	Mar-14	Variance
1	5000	8000	7000	-1000	40	60	35	-25
2	4500	6000	8700	2700	20	65	80	15

Here Variance is always Current Month-Previous month (I want Only one Column for Variance)

I do not want in Script side.

Thanks,

Vinay

Re: Pivot table with Variance

MK_QSL — Tue, 18 Mar 2014 17:29:10 GMT

Difficult to get the same result by Pivot Table but can get the same using Straight Table

Script...

Load

Branch,

Date#(Month,'MMM-YY') as Month,

Sale,

Qty

Inline

[

Branch, Month, Sale, Qty

1, Jan-14, 5000, 40

1, Feb-14, 8000, 60

1, Mar-14, 7000, 35

2, Jan-14, 4500, 20

2, Feb-14, 6000, 65

2, Mar-14, 8700, 80

];

Create a Straight Table

Dimension = Branch

Expressions

For Sale

Expression = SUM({<Month = {"$(=Date(MonthStart(AddMonths(Today(),-2)),'MMM-YY'))"}>}Sale)

Label = Date(MonthStart(AddMonths(Today(),-2)),'MMM-YY')

SUM({<Month = {"$(=Date(MonthStart(AddMonths(Today(),-1)),'MMM-YY'))"}>}Sale)

=Date(MonthStart(AddMonths(Today(),-1)),'MMM-YY')

SUM({<Month = {"$(=Date(MonthStart(Today()),'MMM-YY'))"}>}Sale)

=Date(MonthStart(Today()),'MMM-YY')

SUM({<Month = {"$(=Date(MonthStart(Today()),'MMM-YY'))"}>}Sale)-SUM({<Month = {"$(=Date(MonthStart(AddMonths(Today(),-1)),'MMM-YY'))"}>}Sale)

variance

Same you can use for Qty...

Re: Pivot table with Variance

Gysbert_Wassenaar — Tue, 18 Mar 2014 17:30:13 GMT

If you don't want a script based solution then you cannot use a pivot table. You will have to use a straight table and add separate expressions for each month column and the variance columns.

Re: Pivot table with Variance

Tue, 18 Mar 2014 17:58:21 GMT

Ok Thanks, But I wud like to do in Pivot table.

Vinay