How to show data from the 12 previous months when a sum expression is not been used?

[#1]

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

[sinanozdemir]

Hi Rodrigo,

Make sure that "Suppress Zero Values" check box is unchecked in the presentation tab:

Hope this helps.

[#3]

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

[chaper]

Sort tab - Sort by expression and use [Data do Resultado] and Ascending

[Mark_Little]

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

[#6]

I tryed that calculated dimension:

if("Data do Resultado">=AddMonths(Today(), -11), "Month")

But something is weird, October is not before november, see:

[sinanozdemir]

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?

[Mark_Little]

If that doesn't work, if you did it in script you could also add a order colunm i.e. RollingNo?

Mark

[#9]

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

[Mark_Little]

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