Latest Year Sales Set Anlaysis

[Lakshmanan]

Hi , 

Could someone help with Set analysis for Latest Year and previous of latest year sales calculation .

LOAD * Inline [
OrderDate, Sales
202203, 201
202212, 215
202302, 132
202303, 315
202305, 149
202309, 720
202312, 925
202103, 710
202105, 25
202106, 22
202107, 210
202108, 213
];

Expected sales for 2023 and 2022.

[Oleg_Troyansky · Accepted solution]

Hi,

You have a long way to go from this data to Set Analysis, but I can give you a few ideas:

1. You need to transform your dates (or months) into proper Qlik dates - look up functions Date() and Date#(). With their help you can build a proper date for each one of your textual dates.

2. Then, you should calculate Year and Month as separate fields in your dataset - either in the same table, or in a separate Calendar table - something like this:

LOAD

   ...

   Year(DateField)   as Year,

   Month(DateField) as Month,

...

3. When you have that in your data, your Set Analysis becomes rather simple:

sum( {<Year={$(max(Year))}>}  Sales)

Prior year:

sum( {<Year={$(max(Year)-1)}>}  Sales)

Join us at the Masters Summit for Qlik in Orlando or in Dublin to learn advanced Set Analysis, scripting, data modeling, performance, visualizations, and more - delivered by some of the best Qlik experts in the world!

[Sohan_Patil]

Hi,

In below snapshot both latest and previous year sales are calculated

Latest Year : Sum({<OrderDate = {">=$(=Date(Yearstart(date#(max(OrderDate),'YYYYMM'),0),'YYYYMM'))"}>}Sales)

Previous Year : Sum({<OrderDate = {">=$(=Date(Yearstart(date#(max(OrderDate),'YYYYMM'),-1),'YYYYMM'))<$(=Date(Yearstart(date#(max(OrderDate),'YYYYMM'),0),'YYYYMM'))"}>}Sales)

========================================================================================

Also you can add a field Year in load script :

table:
Load*, Date(Date#(OrderDate,'YYYYMM'),'YYYY') as Year;
LOAD * Inline [
OrderDate, Sales
202203, 201
202212, 215
202302, 132
202303, 315
202305, 149
202309, 720
202312, 925
202103, 710
202105, 25
202106, 22
202107, 210
202108, 213
];

Latest Year : Sum({<Year = {$(=max(Year))}>}Sales)

Previous Year : Sum({<Year = {$(=Date(addyears(max(Year),-1),'YYYY'))}>}Sales)