Max month sales of each Year?

[vidyasagar159]

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.

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

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

Product	SalesDate	SalesAmount
Apple	March-30-2017	$200
Apple	April-30-2018	$400

Thanks,

-Vidya

[MK_QSL]

SET DateFormat='MMM-DD-YYYY';
Data:
Load *,Year(SalesDate) as SalesYear;
LOAD Product, 
     Alt(Date#(SalesDate,'MMM-DD-YYYY'),Date#(SalesDate,'MMMM-DD-YYYY')) as SalesDate, 
     Replace(SalesAmount,'$','') as SalesAmount
FROM
[https://community.qlik.com/thread/304280]
(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))

[vidyasagar159]

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

[shiveshsingh]

F:load *, Year(SalesDate) as Year;

T:LOAD Product,  

     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)

[shiveshsingh]

[isingh30]

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

[shiveshsingh]

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

[isingh30]

Thank you Shivesh for your feedback

[kaanerisen · Accepted solution]

Hi Vidya,

Script:

Load

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

[zebhashmi]

you can close the thread.