Qlik Sense Accumulation - ignore future dates

[GaryMcDonald]

Hello,

I'm hoping someone may be able to help me amend this table.

The attached table simply counts the number of sales by the day of the Month (each months is a separate calculation - Count({<[Transaction Type]={'New Business'},[Created Date.autoCalendar.Month]={'Jul'} >} distinct [Sub Policy Number]))

I accumulate the totals so I can show a the running total by day of the month, however in the current month I only want to show up to yesterday (which is the max Created Date).

As you can see on the attached graph the data in September plateaus as of the 22nd that is because I only have data up to the 21st and would prefer the accumulation to stop at the 21st.

Please could someone explain how I could stop this from calculating past the max date of the current month?

Any help is greatly appreciated.

Thanks

.

[sunny_talwar]

Use this for Sep expression

If([Day of Month] < Day(Today()),
  RangeSum(Above(
    Count({<[Transaction Type] = {'New Business'}, [Created Date.autoCalendar.Month] = {'Sep'}>} DISTINCT [Sub Policy Number])
  , 0, RowNo()))
)

[GaryMcDonald]

Hi Sunny,

Thanks for the reply, unfortunately that doesn't work.  I also amended [Day of month] to Day(Created Date) , and that didn't work.

Thanks

Gary

[sunny_talwar]

@GaryMcDonald  What exactly do you mean when you say it doesn't work? It shows nothing? give incorrect values? What exactly is wrong?

[GaryMcDonald]

Hi sunny,

Apologies not a very helpful response. The calculation doesn't appear to work at all, the September line runs at zero for every data point.

Thanks

Gary