Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Remove extra months while doing a TY vs LY analysis

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

6 Replies
Highlighted
Contributor II
Contributor II

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

Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Highlighted
Partner
Partner

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

Highlighted
Specialist
Specialist

In Expression:

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

than Suppress Zero-Values

Highlighted
Specialist
Specialist

Or

Add Calculated Dimension for Month Field

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

Click Suppress When Value is Null