Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Finding sales on Min date of a month against Month dimension

Hi Guys,

I have a Sales table as below:

Sales:

DateSales
11/1/201610500
11/1/20168000
11/21/201611000
11/30/201617000
11/30/201610200
12/2/201613200
12/14/201617000
12/29/201621000
1/3/201716000
1/10/201718000
1/22/201714000
1/22/201721000

I want an output like the table below

 

MonthYearTotal SalesSales on Min date of MonthSales on Max date of Month
Nov-16567001850027200
Dec-16512001320021000
Jan-17690001600035000

I am using following expressions

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

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

but what I am getting is

MonthYearTotal SalesSales on Min date of MonthSales on Max date of Month
Nov-1656700185000
Dec-165120000
Jan-1769000035000

Could somebody please help?

Thanks in advance.

Regards,

Ritesh Pathak

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

View solution in original post

6 Replies
tresesco
MVP
MVP

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

MK_QSL
MVP
MVP

tresesco's answer should work.

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))

Anonymous
Not applicable
Author

Hi Manish,

Thanks. Your expressions are working.

MK_QSL
MVP
MVP

Have you tried tresesco's answer. That should also work..

Anonymous
Not applicable
Author

Thanks Tresesco. It works.

Anonymous
Not applicable
Author

Yes. That did.