Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Qlik community,
I am trying to create a more dynamic range for the set expression below using the today() function but everything I have tried has not worked. Essentially, I want to return all the OrderID's for FY2018 from the beginning of the Fiscal Year (10/1/2017) up until the date that the app is opened. The work around has been to simply type in the actual month each time we move into the next month but I want this expression to basically update itself.
RangeSum (above(count({$<[ORDERID]={*}>*$<FYear={'FY2018'}>*$<Month={'Oct', 'Nov','Dec','Jan'}>} [ORDERID]),0,RowNo()))
Please help!
Thanks!
Ben mto
I would rather use the data field to do this
RangeSum(Above(Count({$<DateField = {"$(='>=' & YearStart(Today(), 0, 10) & '<=' & Date(Today()))"}, FYear, Month>} [ORDERID]), 0, RowNo()))
Where ='>=' & YearStart(Today(), 0, 10) & '<=' & Date(Today()) in a KPI object should show you the range you need and in the format you have your date field.
Sunny,
Sorry for the late response and thanks for the suggestion.
I was able to solve part of my problem using the following expression:
Measure 1:
RangeSum (above(count({$<DateField ={'>=$(=Date(vFY2017Start))<$(=Date(AddYears(Today(),-1)))'}>} [OrderID]),0,RowNo()))
Measure 2:
RangeSum (above(count({$<DateField ={'>=$(=Date(vFY2018Start))<$(=Date(Today()))'}>} [OrderID]),0,RowNo()))
However, these two rangesum "Trendlines" need to track against a static Trendline that has a set number of targets per month e.g. Note: These numbers do not need to aggregate - they are hard targets per month.
*Measure 3*
Month TargetOrders
Oct, 15
Nov, 30
Dec, 45
Jan, 60
etc, etc.
If I put measures 1 and 2 in without the static trendline, everything works as it should and the line chart populates from October to March (the current month of this posting). However, if I add measure 3, there is no good way to make it only show the data points as defined by a date range. At least, I can't figure out the set expression that will make it work and I have tried a ton.
Can you help?
-Ben