qsvariable with dynamic values - Qlik Community

jillwoodley · ‎2018-04-11

Wondered if anyone can help - I've searched the community and while I find many posts i cant find one that resolves my problem.

My data is organised by date for example:

12/01/2018,data1,data2,data3

12/02/2018,data1,data2,data3

12/03/2018,data1,data2,data3

Next month the table grows and I get new rows for the following month.

I want the page to open with the latest month's data on show but for the user to be able to select a previous months data if they wish.

I have this all working using the extension qsVariable, and set analysis using this variable but I have to go in every month and add the new values for the new date and I am looking for a way to automate this.

I have looked at using the extension qliksenselistbox but this makes a real selection and I only want to set a variable as I have other sheets that are similar but use different dates. In fact I have 4 different sheets all using different dates which is why I want to automate it.

I have seen that the qsvariable has a dynamic setting in values Value1|Value2 - but i do not know how to create the expression that would create 12/03/2018|12/02/2018|12/01/2018 from my table. I don't think I can use peek as I don't know how many different dates i will have in the table so cant set the row number

If anyone has any ideas how i can create the dynamic expression, or any other way of achieving this i'd be most grateful.

Many thanks,

Jill

petter · ‎2018-04-11

I would recommend that you consider having an extra date field that is relative to today. You can call it DaysAgo.

0 is always today. 1 would be yesterday .... and so forth.

The beauty of this is that as long as you fix DaysAgo to be 0 it will always be current. You could of course create additional xxxxAgo fields for Months, Quarters, Weeks and Years too if you need.

This would make your problem disappear as you don't need to dynamically update your selections.

jillwoodley · ‎2018-05-01

Sorry i have not replied sooner but i have been poorly

I am afraid i do not understand the concept behind xxxago field.

When my new data comes in say 1/05/2018 i can fix this so it is 0 daysago so my selection can open with the latest data - but how do my users chose to see the previous set of data?

krishna_2644 · ‎2018-05-01

Hi Jill - will you be able to share a mocked app with atleast 2 months's dates and corresponding faked values?

it would be harder to resolve something like above without proper data.

Johannes · ‎2019-01-29

Hi,
I solved something similar by using dynamic variable with a function
=concat(Year,'|')
Hope that helps
Hannes