topic Re: Last day of every month (set analysis) in QlikView

Last day of every month (set analysis)

Wed, 06 Mar 2013 03:08:17 GMT

Hi everyone,

I need to get sales of last day of every month. Help me pls

dimension: Year-Month

Below expression working, but

( when i select march, it gives day 31) So that i can't get values of february, april , june etc.. and when i choose day 28 it gives me sales of every month. but i

I need expresion to get last days of month (feb-28, march-31, april-30)

Sum({$<

[date Year] = ,

[date Month] = ,

[date Day of month] = {$(=max([date Day of month]))},

$(vSetRolling12)

>} Sales)

And below expressions not working

Sum({$<

[date Year] = ,

[date Month] = ,

[date Day of month] = {$(=FirstSortedValue([date Day of month],-ADATE))},

$(vSetRolling12)

>} Sales)

Sum({$<

[date Year] = ,

[date Month] = ,

[date Day of month] = {$( =Aggr(max({$<

[date Month] = ,

[date Day of month] =

>} [date Day of month])

,[date Month]))},

$(vSetRolling12)

>} Sales)

Thank you

Uguudei E

Re: Last day of every month (set analysis)

kaushiknsolanki — Wed, 06 Mar 2013 06:10:16 GMT

Hi,

You need to create a flag in your script.

say for example, if the the date field you are using is "SalesDate" then use below expression in your script to create a flag for every monthend date.

if(monthend(SalesDate) = SalesDate, 1,0) as MonthEnd_Flag.

this will create a flag in front of every monthend date.

now only thing you need to change in your expression is as below.

Sum({$<

[date Year] = ,

[date Month] = ,

[date Day of month] = ,

MonthEnd_Flag = {"1"}

>} Sales)

hope the logic is clear.

Regards,

Kaushik Solanki

Re: Last day of every month (set analysis)

Wed, 06 Mar 2013 10:34:28 GMT

It's working great.

Thank you so much.

Uguudei E

Re: Last day of every month (set analysis)

Wed, 03 Feb 2016 11:02:17 GMT

That fine in expression also we can write

=if(date(max(MonthEnd(date))),Sales)

will give last every month if you month as dimension.....