10 Replies Latest reply: Jun 11, 2018 2:27 PM by vidya sagar malla

# Max month sales of each Year?

Hi,

I am a simple requirement. I want to show the max month sales of each year of my products. Unfortunately, I am not able to find the logic. Please find some sample data below. I am doing this in Qlik Sense. Thanks in advance.

ProductSalesDateSalesAmount
AppleJan-30-2017\$100
AppleFeb-28-2017\$50
AppleMarch-30-2017\$200
AppleJan-30-2018\$200
AppleFeb-28-2018\$100
AppleMarch-30-2018\$300
AppleApril-30-2018\$400

Now what I want to show is MAX month of every year in my Table or in my Barchart is.

ProductSalesDateSalesAmount
AppleMarch-30-2017\$200
AppleApril-30-2018\$400

Thanks,

-Vidya

• ###### Re: Max month sales of each Year?
```SET DateFormat='MMM-DD-YYYY';
Data:
Alt(Date#(SalesDate,'MMM-DD-YYYY'),Date#(SalesDate,'MMMM-DD-YYYY')) as SalesDate,
Replace(SalesAmount,'\$','') as SalesAmount
FROM
(html, codepage is 1252, embedded labels, table is @1);
```

Create a Straight Table/ Bar Chart

Dimension

Product

=Date(Aggr(Max(SalesDate),SalesYear))

or

=Date(Aggr(Max(SalesDate),SalesYear,Product))

Expression

FirstSortedValue(DISTINCT SalesDate, -Aggr(SalesYear,SalesDate))

or

FirstSortedValue(DISTINCT SalesDate, -Aggr(SalesYear,SalesDate,Product))

• ###### Re: Max month sales of each Year?

Hi, Thanks for looking into this. But where is the SalesAmount in the expression? However, I am getting an error.

• ###### Re: Max month sales of each Year?

Alt(Date#(SalesDate,'MMM-DD-YYYY'),Date#(SalesDate,'MMMMM-DD-YYYY')) as SalesDate,

Replace(SalesAmount,'\$','') as SalesAmount   INLINE [

Product, SalesDate, SalesAmount

Apple, Jan-30-2017, \$100

Apple, Feb-28-2017, \$50

Apple, March-30-2017, \$200

Apple, Jan-30-2018, \$200

Apple, Feb-28-2018, \$100

Apple, March-30-2018, \$300

Apple, April-30-2018, \$400

];

Dimension :  Product , =Date(Aggr(Max(SalesDate),Year),'MMMMM-DD-YYYY')

Measure : Sum( SalesAmount)

• ###### Re: Max month sales of each Year?

=Max({<Year = {2017,2018}>}SalesAmount)

• ###### Re: Max month sales of each Year?

Expression should be flexible with varying year. It should not be hard coded.

• ###### Re: Max month sales of each Year?

Thank you Shivesh for your feedback

• ###### Re: Max month sales of each Year?

Hi Vidya,

Script:

Product,

date(date#(SalesDate,'MMM-DD-YYYY')) as SalesDate,

Month(date#(SalesDate,'MMM-DD-YYYY')) as Month,

Year(date#(SalesDate,'MMM-DD-YYYY')) as Year,

date(date#(SalesDate,'MMM-DD-YYYY'),'MMM-YYYY') as MonthYear,

SalesAmount

Inline [

Product,SalesDate,SalesAmount

Apple,'Jan-30-2017',100

Apple,'Feb-28-2017',50

Apple,'Mar-30-2017',200

Apple,'Jan-30-2018',200

Apple,'Feb-28-2018',100

Apple,'Mar-30-2018',300

Apple,'Apr-30-2018',400

];

Expression:

Sum({<MonthYear={"=aggr(rank(SalesAmount),Product,Year,Month)=1"}>}SalesAmount)

Hope it helps..

• ###### Re: Max month sales of each Year?

Thanks for your help. I never thought that this can be done by using Rank function. I really appreciate your help.