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: 
mbespartochnyy
Creator III
Creator III

12 Months Moving Min and Max for Moving Fields

Hello everyone,

I have a pretty simple database with 5 fields, RowNo, Date, 1 Year Ahead, 2 Years Ahead, and 3 Years Ahead fields:

Moving Fields 4.PNG

I'm trying to figure out a 12 months min and max for a particular year - year that is in 1 Year Ahead Field.


For example, on February 1st, 2017, 1 Year Ahead field contains data for year 2018. However, between January and December 2016, year 2018 data is contained in 2 Years Ahead field.


Another example is November 1st, 2016. Here 1 Year Ahead field contains data for year 2017. Using attached spreadsheet we can see that the 12 months min on November 1st, 2016 is 4.


Does anyone know of a way to get QlikView to figure out a 12 months min for a given date?


Thanks,


Mikhail Bespartochnyy

1 Solution

Accepted Solutions
mbespartochnyy
Creator III
Creator III
Author

Here's the app with solution script for this thread.

View solution in original post

8 Replies
sunny_talwar

In the script or in a front end chart?

sunny_talwar

Actually, I am still trying to figure out what you are trying to get here

mbespartochnyy
Creator III
Creator III
Author

Preferrably in the script and I'm trying to figure out a 12 months min and max for a given date.

Originally, database contained year fields. Data with dates from January 2016 through December 2016 stored values in fields 2017, 2018, and 2019. January, February, and March 2017 had no data populated in field named 2017, but had data for fields 2018, 2019, and 2020.

Instead of having 6 fields (2015-2020), I moved them all into 3, 1 Year Ahead to 3 Years Ahead. Howeve, now using January 1st, 2016 through March 1st, 2017 date range as an exampl, the values for year 2018 were moved to 1 Year Ahead fiel but for dates from last year (Jan - Dec 2016), values from year 2018 are stored in 2 Years Ahead field which creates this problem of me having to reference either 1 or two fields to find a min for a year.

sunny_talwar

I saw your other thread (Moving Fields 12 Months Max and Min) which actually shows the real raw data and also shows the actual complexity of what you are trying to do.

I will keep this in the back of my mind and will try to figure it out at a later time.

mbespartochnyy
Creator III
Creator III
Author

Here's the original database. If it's easier to calculate 12 months min and max for the first available year for a given date in this format of the database, I'll take that as well. I've been hitting a wall with this for the last three days and still can't resolve it. I'm short of just creating a Min and Max fields in a spreadsheet and forgetting trying to write script and expressions to make this work in QlikView. Figured I'd reach out to the community to see if anyone has any suggestions before I turn to Excel.

Mikhail B.

sunny_talwar

While I will also try this out, but in the meantime I would suggest you to create the Min and Max field before you put them into your new bucket of 1 year ahead, 2 year ahead, and 3 year ahead....

mbespartochnyy
Creator III
Creator III
Author

Yeah I figured I'd simplify it and see if anyone will try to solve this one instead and then I'll work through and translate the fix to the more complex problem. I'll appreciate any suggestion you may come up with.

mbespartochnyy
Creator III
Creator III
Author

Here's the app with solution script for this thread.