Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Dynamic Slider: setting Min/Max for Dates for which qty>0

Hi!

I'm trying to set a slider with variables Min and Max in the following scenario:

Orders is a table containing all sort of order data. Here there is also a date field coded as YYYYMMDD

Calendar is a table containg the calendar drawn from the date field in Order table, returning all sort of fields as 'Year', 'Quarter' etcetc.

Calendar is a static table with all pre-written calendar data of a 10 years span, so also in the future.

Therefore, if I set the slider with Min(Date) and Max(Date), the slider returns 2008 and 2020 as Min and Max.

meanwhile I would like to check the Min/Max(Date) for which it exists an entry in the Orders table

Something like

Max(Calendar.Date)  when Orders.QuantityOrdered>0

Is this possible to achieve?

thank you!

1 Solution

Accepted Solutions
Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

Here is an example of what I mean. Hope this helps.

9 Replies
Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

Use two variables, one for the min date with orders > 0 and one for the max date with orders>0. Then set those variables as the min and max of your slider. Does that make sense?

Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

it makes sense from a logic perspective, this is what I have been trying to do.

but from a very down-to-earth script perspective, I'm finding hard to code this down.

If tomorrow I manage I will update here, thank you for your input!

MVP
MVP

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

Hi

Use something like this for the slider min and Max dates

Date(Min(Aggr(If(Count(OrderNumber) > 0, Calender.Date), Calender.Date)))

and

Date(Max(Aggr(If(Count(OrderNumber) > 0, Calender.Date), Calender.Date)))

Hope that helps

Jonathan

Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

Here is an example of what I mean. Hope this helps.

Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

Dear both,

your examples set me somehow on the right direction (especially the example of Rebecca), but still somethign is missing.

I still cannot have the slider to actually filter based on the selection.

I managed to restrict the shown values accordingly if an order exist, but if I select the slider for just a month, it has no impact whatsoever.

slider.JPG

The first one is the slider limited accordingly if orders exist

The second one is a slider just leveraging on the Field OrderDate.

As you see all dates are there (also 2018 when orders are still not existing), but indeed a selection on these is having an effect

For the "limited" slider I input the following

slider_variable.JPG

where vSliderMin

=Date(min(Aggr(If(Count(LQORD)>0,OrderDateCal.Date),OrderDateCal.Date)))

or (according to Rebecca example)

=Date(min({$<LQORD={'>0'}>}OrderDateCal.Date),'YYYYMMDD')

and vSliderMax is like above, (max instead of min, of course!).

vSliderSetUp and vSliderSetDown are used for the multi value selection, and the value attached to it is changing according to the made selection.

If I check in variable overview, actually the numebr shown for these two is a 5 digit one, so I'm afraid that this is avoid the correct "link" between the selection on the slider and the dates op be shown (as they are coded as YYYYMMDD)

any suggestion?

thank you!

Highlighted
Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

The variables will not affect your data unless you use set analysis.  See the attached example.

Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

Dear Rebecca,

thank you for your kind answer!

For my understanding, is this "set analysis" ? I was stumbling upon it in other discussions and I'm not sure whether it is "simply" writing an expression with variables in a chart...

set analysis.JPG

your example works divinely, I hope to be able to transpose this in my qvw, as it has not gone well so far...

Will let you know my results!

thank you

Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

No problem. Set analysis is just how you modify an expression with the brackets.  For example, you can have the expression Sum(Sales), which will give you the sum of all the sales and will be affected by the user's input.  So if the user selects a certain Sales Rep, then it will show the sum of all sales for only that Sales Rep.  Set analysis is a way to modify that without forcing a user's selections.

So, Sum({$<Year={'2012'}>} Sales) is the set analysis that will give you the sum of all sales in the year 2012.  If the user selects just a certain sales rep, for example, then it will show the 2012 sales for only that sales rep.  If you used Sum({1<Year={'2012'}>} Sales), then you would get all sales for 2012 regardless of the user's selections.  This is just a brief overview, but there are a lot of resources on this forum to help you out further. Hope that helps a bit!

Not applicable

Re: Dynamic Slider: setting Min/Max for Dates for which qty>0

sorry for the delay, but hadn't any spare time to work on QV yesterday.

The example you provided could easily be adapted to my situation, and it is wonderfully working!

and thanks also for the brief but comprehensive "set analysis" expalanations above: more than enough to trigger my futher research about it!

Community Browser