3 Replies Latest reply: Aug 19, 2012 11:05 PM by Boris Gerchikov

# How to get sales only for last day of the week,month,Quarter,Year(Dimensions)

I want to display sales count only for the last of any given dimensions of week,month,Quarter,year. I have stored dimension in a variable my problem is getting the expression to check for max(date) for the given dimension. The given expression below returns null.

=Sum({\$<day={"<=\$(=max(day))"}>}Sales)

• ###### Re: How to get sales only for last day of the week,month,Quarter,Year(Dimensions)

Hi Praveenu,

You are consluting for the Max day, not for last day. Depends on the format of the day field, but it could be Max(day)-1.

Anyway, in this expression, witch is the variable? You ask for max(date) in your question, but in the expression i can't find date, and its format is important. I think if you send a copy we could help you more.

• ###### Re: How to get sales only for last day of the week,month,Quarter,Year(Dimensions)

Hi Praveen,

Have you considered using FirstSortedValue()? Something like this...

FirstSortedValue(Sales, -week)

http://qlikshare.com/392

Cheers,

DV

www.QlikShare.com

• ###### Re: How to get sales only for last day of the week,month,Quarter,Year(Dimensions)

Hi Praveen,

I guess you have calendar your sales are assosiated with. So, you can setup YLD, WLD etc last dates as follows:

If(MonthEnd(DAY_DESC) = DAY_DESC,1,0) as MLD

using MonthEnd, YearEnd and other date functions.

Then use these fields in Set Analysis:

MLD={1}

Hope it will help