Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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.
Ok Thanks, But I wud like to do in Pivot table.
Vinay