Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
Giselle100
New Contributor III

Current Year vs Last Year

Hi 

I have attached a sheet with data represented in seasons. 

The expression for the measure is: sum({<Year={$(vMaxYear)}>} [Nett Sale Qty]).

So a bit of context, every year has two seasons, summer and winter. Eg: 2017 (W17, S17), 2018(W18, S18)

What will my expression be if i choose W18 as my current year and want to return W17 as my last year?

Also if I choose W17 & W18 together and want to return W16 & W15?

 

 

7 Replies

Re: Current Year vs Last Year

I think it would be easier for you to do this if you create a new field where you assign a numeric value to W and S. Example -

W17 = 117
S17 = 217
W18 = 118
S18 = 218

So, essentially W = 1, and S = 2

Now when you select W18 which is 118, you can simply do 118-1 = 117 in set analysis like this

{<FieldNameText, FieldNameNum = {"$(=Max(FieldNameNum) - 1)"}>}
Giselle100
New Contributor III

Re: Current Year vs Last Year

Hi Sunny

Thank you for your reply.

Could you show me how to Set W=1 and S=2?

Also, will this work if I choose two season and want the value of the previous two seasons to be returned?

Re: Current Year vs Last Year

How exactly are you loading your data? Can you share the script? It can work when you select multiple rows, but you will have to program it like that....

Giselle100
New Contributor III

Re: Current Year vs Last Year

I've only pulled in these qvds in my Qliksense App. I have not made any manipulations to the script.

The field that pertains the W17, S17, etc is called "Style Season Code" and

the measure that I want to use is "Nett Sale Qty".

 

Calendar:
LOAD
"Date",
"SK Day No",
"SK Ldow No",
"SK Pyr Ldow No",
"Month Start Date",
"Month End Date",
"Week Start Date",
"Week End Date",
"Previous Year Month End Date",
"Previous Year Week End Date",
"Season Start Date",
"Year Start Week End Date",
"Year",
"Month Name",
"Month",
"Week",
"Year Season Code",
"Year Season Description",
"Month Code",
"Season Code",
"Season Description",
"Calendar Type"
FROM [calendar.qvd](qvd);

Products:
LOAD
"SK SKU No",
"Pdiv Id No",
Division,
Department,
"Class",
Subclass,
Style,
SKU,
"Style Season Code",
"Colour Description",
Size
FROM [products.qvd](qvd);

Locations:
LOAD
"SK Brn No",
"Store Type Code"
FROM [locations.qvd](qvd)
where "Store Type Code" = 'B';

Sales:
LOAD
"SK Brn No",
"SK SKU No",
LDOW,
"SK LDOW No" as "SK Ldow No",
"Adj Qty",
"Nett Sale Qty",
"Binc Nett Sale Sell Amt"
FROM [summ_brn_sku_wk_slstkmv_*.qvd](qvd);

 

Re: Current Year vs Last Year

May be just do this

MappingLoad:
Mapping
LOAD * INLINE [
Text, Number
W, 1
S, 2
];

and then add MapSubString...

Products:
LOAD
"SK SKU No",
"Pdiv Id No",
Division,
Department,
"Class",
Subclass,
Style,
SKU,
"Style Season Code",
MapSubString('MappingTable', "Style Season Code") as "Style Season Code Num",
"Colour Description",
Size
FROM [products.qvd](qvd);

 

Giselle100
New Contributor III

Re: Current Year vs Last Year

I get the attached error message with below code.

 

MappingLoad:
Mapping
LOAD * INLINE [
Text, Number
W, 1
S, 2
];


Products:
LOAD
"SK SKU No",
"Pdiv Id No",
Division,
Department,
"Class",
Subclass,
Style,
SKU,
"Style Season Code",
MapSubString('MappingTable', "Style Season Code") as "Style Season Code Num",
"Colour Description",
Size
FROM [lib://QlikData/ack_products.qvd](qvd);

Re: Current Year vs Last Year

My bad, change the MappingLoad to MappingTable:

MappingTable:
Mapping
LOAD * INLINE [
Text, Number
W, 1
S, 2
];