6 Replies Latest reply: Jan 26, 2017 11:11 AM by Ritesh Pathak

# Finding sales on Min date of a month against Month dimension

Hi Guys,

I have a Sales table as below:

Sales:

 Date Sales 11/1/2016 10500 11/1/2016 8000 11/21/2016 11000 11/30/2016 17000 11/30/2016 10200 12/2/2016 13200 12/14/2016 17000 12/29/2016 21000 1/3/2017 16000 1/10/2017 18000 1/22/2017 14000 1/22/2017 21000

I want an output like the table below

 MonthYear Total Sales Sales on Min date of Month Sales on Max date of Month Nov-16 56700 18500 27200 Dec-16 51200 13200 21000 Jan-17 69000 16000 35000

I am using following expressions

=Sum({<Date ={'\$(=Min(Date))'}>}Sales)

=Sum({<Date ={'\$(=Max(Date))'}>}Sales)

but what I am getting is

 MonthYear Total Sales Sales on Min date of Month Sales on Max date of Month Nov-16 56700 18500 0 Dec-16 51200 0 0 Jan-17 69000 0 35000

Regards,

Ritesh Pathak

• ###### Re: Finding sales on Min date of a month against Month dimension

Try like:

=FirstSortedValue(Distinct Aggr(Sum(Sales),MonthYear,Date), Date)    // for Min date sales

=FirstSortedValue(Distinct Aggr(Sum(Sales),MonthYear,Date), -Date)   // for max date sales

• ###### Re: Finding sales on Min date of a month against Month dimension

Thanks Tresesco. It works.

• ###### Re: Finding sales on Min date of a month against Month dimension

There is another way to achieve this.. just for your reference.

Dimension

MonthYear

Expression

SUM(Sales)

Sum(If(Date = Aggr(NoDistinct Min(Date),MonthYear),Sales))

Sum(If(Date = Aggr(NoDistinct Max(Date),MonthYear),Sales))

• ###### Re: Finding sales on Min date of a month against Month dimension

Hi Manish,