Find out how many months back a user_selection is?

[datanibbler]

Hi,

I have, on the GUI of my app, a listbox with month_year combinations like

- Dez_14

- Jan_15

- Feb_15

... each month, one column will be added to those.

=> Based on the user's selection of these, i have to dynamically find out what was the month before

==>> Example: If the user selects "Jan_15", then that shall be displayed in the chart - and the month before which is "Dez_14".

Can anyone give me an idea how to do this without using a really long IF() formula?

Thanks a lot!

Best regards,

DataNibbler

[giakoum]

Do it in the reload script :

Take a distinct list of month_year and use the Previous function to get the value before. No heavy calculations needed in the front end this way...

[marcus_sommer]

Hi DataNibbler,

if you have also numeric fields to these string-field (is recommended) in your datamodel you could use them like:

only({< NumericMonth = {"$(=max(NumericMonth)-1)"}>} StringMonth)

then the user selection would it return.

- Marcus

[datanibbler]

Thanks ioannis!

There's a reason though why I wrote "on the GUI" in my post 😉 I cannot yet think of any way to do it in the script:

The user_selections are made on the GUI - the user decides which months he/ she wants to see - and the values I have in the script (loaded from an Excel I have prepared for general use) only include month_names, no year.

=> So the PREVIOUS() function used on that table would not even work because "Jan" is the very 1st line of the table.

I have a variable containing the current year. Combining those two gives me the values I have in the listbox on my GUI

=> Ex.: Now (in Feb), only Jan_15 is selectable, so the month prior to that would be Dec_14. In a function, I'd need the parameter -2 to get that.

=> In March, Feb_15 or Jan_15 will be selectable. If Feb_15 is selected, I will need the parameter -2 to
     dynamically get to Jan_15, the month prior to that. <=> if Jan_15 is selected, I would need the parameter -3 to get
     to Dec_14, the month prior to that.

=> If I could find out how many months back (from a dynamically calculated Today() the user_selection is, then I would just have to add 1 to get the parameter needed to dynamically go back to that month's prior_month ...

[datanibbler]

Hi,

I will try some "thinking out of the box" and simplify that by adding a little bit of logic to the underlying Excel_file (the one I use for the month_names) => I will calculate in that file the number of months I have to go back from "Today" to every one of the months listed there - that will give me, e.g. in December

     - a value of 1 to go back to Nov

     - 2 to go back to Oct

     - 11 to back to Jan

Then

- in December there will be data in the file for all the months from Jan through Nov, so those months will be
   selectable in that listbox.

=> If Nov_15 is selected, I will get from that table a value of 1 - meaning that to get the month prior to that, I will need
     2, so I just need to add 1 to that value.

=> If Jan_15 is selected, I will get 11 - meaning that to get the month prior, I will need 12, so I need to add 1.

==>> Done!!

Thank you!

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

why don't you use a transforming into numeric values within the data-load like:

date(date#('Nov_15', 'MMM_YY'), 'DD.MM.YYYY')

- Marcus

[simenkg]

In your calendar:

Load *,

      Autonumber(MonthYear,'MonthYears') as MonthYearNo;

Then in your gui you can reference the previous month as:
Only({<MonthYearNo={$(=Max(MonthYearNo)-1)}>} MonthYear)

or in a calculation: Sum({<MonthYearNo={$(=Max(MonthYearNo)-1)}>} Amount)

[datanibbler]

Hi all,

thanks for all your suggestions!

However, I'm happy with the solution I have finally found. And I can put that right back INTO the box because QlikView can of  course do that calculation, too - I just calculate dynamically in the LOAD one field with the dynamically calculated nr. of months I have to go back from "Today" to the month in that line.

Then I just add 1 and I have the parameter I need for the month before that.

=> Voila, I have the names of the two fields that I need to display.

I'll see if it works.