Set expression with date range using Today()

[benwashburne]

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‌

[sunny_talwar]

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.

[benwashburne]

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