Sum expression between two dates

[#1]

I've read lots of other posts about how to write this sort of expression, but no matter what I do I just cannot get Qlikview to display anything other than 0, or the sum of the Workday field for the whole year.

Attached is the data I am importing from an Excel spreadsheet,

I'm trying to write an expression that sums the Workday field between today (or another date as a variable) and the end of the same month i.e. from 13/03/2017 to 31/03/2017.

Next month the upper end of the date range will be 30/04/2017 etc etc.

I've spent hours on this already, and so many different expressions I haven't bothered to include them in this post as I might just confuse everyone.

Any help much appreciated.

[#2]

That works.

But is there any way of assigning:

=Sum({<CalDate = {"$(='>=' & Date(vMaxInvoiceDate) & '<=' & MonthEnd(vMaxInvoiceDate))"}>}WorkDay)

to a variable?

Writing that in every expression that needs the number of working days left in the month is a bit long winded.

[sunny_talwar]

You mean this whole thing?

='>=' & Date(vMaxInvoiceDate) & '<=' & MonthEnd(vMaxInvoiceDate)

I think you should be able to do this.

in variable overview, create a new variable called vVar (or whatever you want to call it) and assign it this value

='>=' & Date(vMaxInvoiceDate) & '<=' & MonthEnd(vMaxInvoiceDate)

and then try this

=Sum({<CalDate = {"$(=vVar)"}>}WorkDay)

or

=Sum({<CalDate = {"$(=$(vVar))"}>}WorkDay)

[#4]

I was thinking of assigning the result of this 'calculation' to a variable:

=Sum({<CalDate = {"$(='>=' & Date(vMaxInvoiceDate) & '<=' & MonthEnd(vMaxInvoiceDate))"}>}WorkDay)

as once this has been calculated as part of an upload, the value won't change, until the next upload, or data refresh.

[sunny_talwar]

The whole sum of workdays? You don't want this value to change?

[#6]

Only when new data is loaded and the vMaxInvoiceDate changes