Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ritespathak10
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
Not applicable

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

6 Replies
tresesco
Not applicable

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

MK_QSL
Not applicable

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

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

ritespathak10
Not applicable

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

Hi Manish,

Thanks. Your expressions are working.

MK_QSL
Not applicable

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

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

ritespathak10
Not applicable

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

Thanks Tresesco. It works.

ritespathak10
Not applicable

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

Yes. That did.