QlikView Administration

[Anonymous]

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

[tresesco · Accepted solution]

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]

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]

Hi Manish,

Thanks. Your expressions are working.

[MK_QSL]

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

[Anonymous]

Thanks Tresesco. It works.

[Anonymous]

Yes. That did.