topic Re: Current Year Vs Prior Year Script in App Development

Current Year Vs Prior Year Script

NemoAndStitch22 — Sat, 16 Nov 2024 20:34:48 GMT

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.

Please could someone help?

Thanks

Re: Current Year Vs Prior Year Script

Vegar — Wed, 26 Jun 2019 12:27:47 GMT

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;

Re: Current Year Vs Prior Year Script

NemoAndStitch22 — Wed, 26 Jun 2019 12:43:04 GMT

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

Re: Current Year Vs Prior Year Script

Vegar — Wed, 26 Jun 2019 12:58:54 GMT

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

Re: Current Year Vs Prior Year Script

NemoAndStitch22 — Wed, 26 Jun 2019 13:15:59 GMT

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

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

Do you know why this would be?

Re: Current Year Vs Prior Year Script

Vegar — Wed, 26 Jun 2019 21:33:06 GMT

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