Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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..
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))
Hi, Thanks for looking into this. But where is the SalesAmount in the expression? However, I am getting an error.
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)
=Max({<Year = {2017,2018}>}SalesAmount)
Expression should be flexible with varying year. It should not be hard coded.
Thank you Shivesh for your feedback
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..
you can close the thread.