Skip to main content

# QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
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:

 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

Could somebody please help?

Thanks in advance.

Regards,

Ritesh Pathak

1 Solution

Accepted Solutions
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

6 Replies
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

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.

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.

Community Browser