Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a pivot table in my sheet, where i need to show data only of the previous 12 months.
That pivot table has 2 dimensions "KPI" and "Month".
The field "Month" is a result of the function Date#, as described below:
Date#([Data do Resultado], 'MM/YYYY') as [Data do Resultado],
Year([Data do Resultado]) as Year,
Month([Data do Resultado]) as Month,
My expression is just the field [Status Month] , which says if it is Red or Green, by the below expression:
([Status Month] = 'Vermelho', LightRed(), if([Status Month] = 'Verde', LightGreen(), NULL()))
Thats what i see right now in my pivot table:
I dont know why "February" is not been displayed, and it only shows 11 months, if i have more than 12 months of data.
Anyone could help me to create the rule to display the last 12 months without a sum expression?
Thanks
Hi Rodrigo,
Make sure that "Suppress Zero Values" check box is unchecked in the presentation tab:
Hope this helps.
Hi Sinan,
Thanks for your response.
Now i see 12 months, but not the last 12 previous months..
How should i classify to sort the months as the last previous from the current, for ex:
Dec/2014 | Jan/2015 | Feb/2015 | Mar/2015 | Apr/2015 | May/2015 | Jun/2015 | Jul/2015 | Aug/2015 | Sep/2015 | Oct/2015 | Nov/2015
Regards
Sort tab - Sort by expression and use [Data do Resultado] and Ascending
Hi,
Could you not mark a 12 month rolling period in your script? to flag this months and previous 12 and then and the flag to your if statement? or a calculated diemension?
Mark
I tryed that calculated dimension:
if("Data do Resultado">=AddMonths(Today(), -11), "Month")
But something is weird, October is not before november, see:
It seems like it is sorting by using the expression. Is October number greater than November?
Try chaiperi's suggestion. Does that work at all?
If that doesn't work, if you did it in script you could also add a order colunm i.e. RollingNo?
Mark
It doesn´t work Sorting by expression and using [Data do Resultado] and Ascending, it returned the same result.
Mark, could you please provide an example of how to add a order colum (RollingNo).
Thanks
Hi,
It would be an embedded if statement i have started it below.
Data:
Load *, IF(Date(Date,'DD/MM/YYYY')>=(Addmonths(Monthstart(today()),-11)),1,0) as Rolling,
IF(Date(Date,'DD/MM/YYYY')=(Addmonths(Monthstart(today()),-11)),12,
IF(Date(Date,'DD/MM/YYYY')=(Addmonths(Monthstart(today()),-10)),11)) as RollingNo;
LOAD * INLINE [
Info, Date
1, 17/11/2015
2, 01/10/2015
3, 01/09/2015
4, 01/08/2015
5, 01/07/2015
6, 01/06/2015
7, 01/05/2015
8, 01/04/2015
9, 01/03/2015
10, 01/02/2015
12, 01/01/2015
13, 01/12/2014
14, 01/11/2014
];
You would need to finish the if start to mark the rest of the months