Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vidyasagar159
Creator II
Creator II

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

1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

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)

Untitled.png

Hope it helps..

View solution in original post

10 Replies
MK_QSL
MVP
MVP

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
Creator II
Creator II
Author

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

MaxMonth in Year.PNG

shiveshsingh
Master
Master

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

Capture.JPG

isingh30
Specialist
Specialist

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

r.PNG

shiveshsingh
Master
Master

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

isingh30
Specialist
Specialist

Thank you Shivesh for your feedback

kaanerisen
Creator III
Creator III

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)

Untitled.png

Hope it helps..

View solution in original post

zebhashmi
Specialist
Specialist

you can close the thread.