topic Re: 12 Months Moving Min and Max for Moving Fields in QlikView

12 Months Moving Min and Max for Moving Fields

mbespartochnyy — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

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

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

Re: 12 Months Moving Min and Max for Moving Fields

sunny_talwar — Wed, 01 Mar 2017 17:16:27 GMT

In the script or in a front end chart?

Re: 12 Months Moving Min and Max for Moving Fields

sunny_talwar — Wed, 01 Mar 2017 17:21:34 GMT

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

Re: 12 Months Moving Min and Max for Moving Fields

mbespartochnyy — Wed, 01 Mar 2017 18:04:09 GMT

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.

Re: 12 Months Moving Min and Max for Moving Fields

sunny_talwar — Wed, 01 Mar 2017 18:46:37 GMT

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.

Re: 12 Months Moving Min and Max for Moving Fields

mbespartochnyy — Wed, 01 Mar 2017 18:47:11 GMT

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.

Re: 12 Months Moving Min and Max for Moving Fields

sunny_talwar — Wed, 01 Mar 2017 18:49:18 GMT

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

Re: 12 Months Moving Min and Max for Moving Fields

mbespartochnyy — Wed, 01 Mar 2017 18:49:39 GMT

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.

Re: 12 Months Moving Min and Max for Moving Fields

mbespartochnyy — Thu, 02 Mar 2017 18:24:50 GMT

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