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

[#1]

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)

[sebastiandperei]

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.

[IAMDV]

Hi Praveen,

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

FirstSortedValue(Sales, -week)

I've made videos on using FirstSortedValue(). Please check them...

http://qlikshare.com/392

Cheers,

DV

www.QlikShare.com

[bgerchikov]

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