Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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
Highlighted

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)"}>}
Highlighted
Contributor III
Contributor III

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?

Highlighted

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

Highlighted
Contributor III
Contributor III

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);

 

Highlighted

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);

 

Highlighted
Contributor III
Contributor III

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);

Highlighted

My bad, change the MappingLoad to MappingTable:

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