topic Re: Remove extra months while doing a TY vs LY analysis in QlikView

Remove extra months while doing a TY vs LY analysis

amithmurali — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Remove extra months while doing a TY vs LY analysis

Anonymous — Thu, 28 Jul 2016 06:56:47 GMT

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

Re: Remove extra months while doing a TY vs LY analysis

Anil_Babu_Samineni — Thu, 28 Jul 2016 07:25:06 GMT

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

Re: Remove extra months while doing a TY vs LY analysis

kaushiknsolanki — Thu, 28 Jul 2016 07:28:33 GMT

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

Re: Remove extra months while doing a TY vs LY analysis

amithmurali — Thu, 28 Jul 2016 07:29:02 GMT

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

Re: Remove extra months while doing a TY vs LY analysis

tyagishaila — Thu, 28 Jul 2016 07:29:52 GMT

In Expression:

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

than Suppress Zero-Values

Re: Remove extra months while doing a TY vs LY analysis

tyagishaila — Thu, 28 Jul 2016 07:53:40 GMT

Add Calculated Dimension for Month Field

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

Click Suppress When Value is Null