Last day of every month (set analysis)

[#1]

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

[kaushiknsolanki · Accepted solution]

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

[#3]

It's working great.

Thank you so much.

Uguudei E

[#4]

That fine in expression also we can write

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

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