Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot table with Variance


Hi all,

I have a data like...

                                                              

BranchMonthSaleQty
1Jan-14500040
1Feb-14800060
1Mar-14700035
2Jan-14450020
2Feb-14600065
2Mar-14870080

 

  and I want Pivot Table like

                                                                      

SaleQty
BranchJan-14Feb-14Mar-14VarianceJan-14Feb-14Mar-14Variance
1500080007000-1000406035-25
2450060008700270020658015

 

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

I do not want in Script side.

Thanks,

Vinay

3 Replies
MK_QSL
MVP
MVP

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...

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Vinay