Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Giselle100
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
sunny_talwar

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
Contributor III
Contributor III
Author

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?

sunny_talwar

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
Contributor III
Contributor III
Author

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

 

sunny_talwar

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
Contributor III
Contributor III
Author

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

sunny_talwar

My bad, change the MappingLoad to MappingTable:

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