Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Here is an example of what I mean. Hope this helps.
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?
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!
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
Here is an example of what I mean. Hope this helps.
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.
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
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!
The variables will not affect your data unless you use set analysis. See the attached example.
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...
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
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!
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!