Remove extra months while doing a TY vs LY analysis

[amithmurali]

Hi guys,

I have to compare this year sales with last year sales in a pivot with month as dimension.

when i try to get the results it will show me negative values for the missing months in the current year.

i used formula (this year - last year). i want to show only the months which are present in the max(year).

[Anonymous]

The Variable at script level:

vCurrMonth = Num(MonthEnd(Today(1)))

Create a flag with Sales date:

If(Num(SALEDATE)<= $(vCurrMonth),1,0) as HistDataFlag

In Chart:

Add the HistDataFlag={1} in your Set Expression.

Thanks

Nimesh

[Anil_Babu_Samineni]

If values or correct then try like this

Go to --> Number Tab --> Select That expression and choose Integer then Format Pattern should be (#,##0); (#,##0)

OR Else, In fact, if you want to help try to post either expression / Application which demonstrates the Issue

- ANIL

[kaushiknsolanki · Accepted solution]

Hi,

I guess you have created 2 Expression. If yes then your expression should be like this.

1. This Year

    Sum(Sales)

2. Last Year

     Sum({<Year = {"$(=Max(Year)-1)"},Month = {"<=$(=Max({<Year = {"$(=Max(Year))"}>}Month))"}>}Sales)

Regards,

Kaushik Solanki

[amithmurali]

thanks for the reply. but my issue here is, the diff may be either +ve or negative and for rest all months which is not present in the max(year) it is always -ve.

I wanted to filter the months which is present in the max(Year).

[tyagishaila]

In Expression:

if(Month <= Month(MaxDate), Your Expression, 0)

than Suppress Zero-Values

[tyagishaila]

Or

Add Calculated Dimension for Month Field

if(MonthField <= Month(MAXDATE), MonthField , ' ')

Click Suppress When Value is Null