Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
NemoAndStitch22
New Contributor III

Current Year Vs Prior Year Script

Hiya, 

 

I'm looking to add in the script a bit to categorise as prior year vs current year. 

So all my files are labelled the same With the differences being the Month and Year. 

What I would like to be able to do is have Qliksense identify that the latest file in date is May 2019, and that is categorised as Current Year, and then have it find the one that's May 2018 and have that categorised as Prior Year so I can compare the two in multiple tables. 

I can't seem to find something a previous thread that works/gives me what I need, but I have got Qlik to recognise the May 2019 etc as a date and this is my script to do that.

 

Script.PNGSJL snip.PNG

Please could someone help? 

Thanks

5 Replies
Partner
Partner

Re: Current Year Vs Prior Year Script

You could solve it like this:

map:
mapping LOAD * 
inline [
  A1, B1
  0, Current Year
  -1, Prior Year
];

Data:
LOAD 
  Period , 
  Year 
INLINE [
  Period, Year
  Mar, 2019
  Apr, 2019
  Feb, 2018
  Jul, 2017
];
JOIN LOAD 
  MAX(Year) as LatestYear 
RESIDENT 
  Data;

LEFT JOIN LOAD
  applymap('map',Year - LatestYear, null()) as RelYear,
  *
RESIDENT Data;
DROP FIELD LatestYear;
???
Celebrating my 300 accepted solution in the Qlik Community
???
NemoAndStitch22
New Contributor III

Re: Current Year Vs Prior Year Script

Hi Vegar,

 

Thanks for this, but I'm not sure it will work in the long run, There will be data for every month going back to 2016 and I don't want to have to edit the script every month when this is run, especially as I might not be the one running it each time... 

 

Is there another way?

 

Thanks,

 

Megan

Partner
Partner

Re: Current Year Vs Prior Year Script

Oh, I think I misslead you, you don't have to do monthly adjustments with my solution.

You should replace the part below with your own data, the script reading file names. I just used an inline[]  as simple way to show you the concept. 

Data:
LOAD 
  Period , 
  Year 
INLINE [
  Period, Year
  Mar, 2019
  Apr, 2019
  Feb, 2018
  Jul, 2017
];

 

???
Celebrating my 300 accepted solution in the Qlik Community
???
NemoAndStitch22
New Contributor III

Re: Current Year Vs Prior Year Script

Ah okay, gotcha thank you, when I do this, and adapt to my data it doesn't like the Year field...

 

Error.PNG

 

Every time I have year written it's the same.. 

Do you know why this would be? 

Partner
Partner

Re: Current Year Vs Prior Year Script

It looks like you haven't managed to create a year field at the point you
are referring to it. I have no better guess without looking at your
script/data model.

-Vegar
???
Celebrating my 300 accepted solution in the Qlik Community
???