topic Re: Using slider to set a variable in QlikView

Using slider to set a variable

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

Hello everyone

I have a slider that is set up to act as a period selector.

Min value and max value are defined as follows

'=date#(min({1} date_YYYYMM),'YYYYMM')'

'=date#(max({1} date_YYYYMM),'YYYYMM')'

I use these periods in my set analysis, for example:

Count({$<[order_date_YYYYMM]={">=$(=min(date($(vPeriod3),'YYYYMM')))<=$(=max(date($(vPeriod4),'YYYYMM')))"} ,$(vExp_Unselect_datefields), $(vExternalClients),$(vOnlyBOWithKnownLT),$(vExcludingArticleTypes)>} so_ordernumber)

Now I face 2 problems:

1. When sliding up & down my 12 month selection seems to have 12 or 13 months. Probably the period is somewhere between 12 and 13 months, where I would like to have exactly 12. I thought that having static step would prevent this.

2. My variable vPeriod3/4 often becomes a value with digits for example 42890,4. This causes my calculations to be wrong.

Re: Using slider to set a variable

marcus_sommer — Mon, 16 Oct 2017 13:20:00 GMT

A value of 42890,4 means that you haven't a date else it's a timestamp. You could with:

date(floor([order_date_YYYYMM])) as [order_date_YYYYMM]

transform your timestamp into a date. And quite probably I would do it directly within the script and not just within the mentioned variables/expressions.

- Marcus

Re: Using slider to set a variable

thomasreynaert — Mon, 16 Oct 2017 13:50:14 GMT

These are all dates, no time stamps.

Thanks for your help!

Re: Using slider to set a variable

marcus_sommer — Mon, 16 Oct 2017 13:56:38 GMT

To see a date doesn't mean mandatory that it is really a date - it could be just formatted. And 42890,4 is definitely a timestamp - just use in a textbox: timestamp(42890.4) to see the result.

- Marcus

Re: Using slider to set a variable

thomasreynaert — Mon, 16 Oct 2017 14:06:27 GMT

Indeed but there is no formatting set to date. In fact if the choice is made to display decimals, it will display ,00. Also the source data is only a date, no time-stamp.

Re: Using slider to set a variable

marcus_sommer — Mon, 16 Oct 2017 14:09:20 GMT

How are your variables vPeriod3/4 created?

Re: Using slider to set a variable

thomasreynaert — Mon, 16 Oct 2017 14:12:23 GMT

Well they are dynamic, since the user can drag the slider and thereby changing the variables.

Re: Using slider to set a variable

marcus_sommer — Mon, 16 Oct 2017 14:55:43 GMT

I think your min/max isn't like you expected it - just try them within a textbox:

=date#(min({1} date_YYYYMM),'YYYYMM')'

=date#(max({1} date_YYYYMM),'YYYYMM')'

and think it should be rather like:

floor(min({1} date_YYYYMM)) // maybe without a formatting

- Marcus

Re: Using slider to set a variable

thomasreynaert — Mon, 16 Oct 2017 15:39:08 GMT

Both formulas will give me 201501 or 201712 respectively as expected. These variables are used to define the start and end of the slider. The problem is the bar that defines the periods.

Thanks for your perseverance

Re: Using slider to set a variable

marcus_sommer — Mon, 16 Oct 2017 15:54:02 GMT

What returned min/max({1} date_YYYYMM) within a textbox by different selections? Always an integer?

Re: Using slider to set a variable

thomasreynaert — Mon, 16 Oct 2017 15:59:54 GMT

Well if I would leave the formula like it is, the result won't change based on selection.

With min/max({$} date_YYYYMM), the result will change. However still always as integer

Re: Using slider to set a variable

marcus_sommer — Mon, 16 Oct 2017 17:10:15 GMT

If I look again on your slider I'm not sure if a date is really suitable to create a slider with 12 month between min and max. Months and even years are not homogenous over a longer period and therefore the selected range might not always fit exactly. I could imagine that a period-field is more suitable.

- Marcus

Re: Using slider to set a variable

thomasreynaert — Tue, 17 Oct 2017 13:11:31 GMT

I'm not sure if I understand correctly. The fields that I use are link_date_YYYYMM, I don't see how any other date field could change the problem. For example I could use link_date with all the dates, but that will give the same range except that I need the user to select by month.

Re: Using slider to set a variable

thomasreynaert — Wed, 18 Oct 2017 15:35:47 GMT

I found a solution to my 2nd problem.

Apparently setting static step to '2' instead of '1' seems to avoid decimals appearing. I believe setting it to 1 would also do the job.

My 1st problem remains, in fact QV gives me a lower granularity than I need. I just need 36 monts (2015-2017) instead of 1096 days.

Re: Using slider to set a variable

marcus_sommer — Thu, 19 Oct 2017 08:03:56 GMT

This is what I mentioned above that a daily level won't be homogenous enough to select a period of 12 months over several years. But you could use a period-field for it mayve created within the master-calendar with something like this:

(year(Date) - 2000) * 100 + month(Date)

dual(date(Date, 'YYMM'), autonumber(year(Date) & '|' & month(Date)))

- Marcus