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.

[sunny_talwar · Accepted solution]

This seems to be working

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

[#3]

Yes, that works perfectly. Thank you very much

Sorry, one more question:

But what if instead of using Today() in the expression I used another field i.e. InvoiceDate.

If I established the most recent invoice date using:

Set vMaxInvoiceDate = max(InvoiceDate):

where invoice date is a field in another table - imported from a CSV file

Date#(left(Date,10),'DD/MM/YYYY') AS InvoiceDate,

This is the expression I am actually trying to create.

I seem to be able to use the vMaxInvoiceDate variable when comparing to other variables like:

Set vMaxInvoiceDay = Day($(vMaxInvoiceDate));

Set vDaysInMaxMonth = Day(MonthEnd(Month($(vMaxInvoiceDate))));

Set vRunRateFactor = ($(vDaysInMaxMonth) - $(vMaxInvoiceDay))/$(vMaxInvoiceDay);

But not when using as criteria to extract data from the Calender table

I am really struggling with Dates in Qlikview

[sunny_talwar]

This Max(InvoiceDate) changes based on selections or is this static value and you only expect it to change when the application refreshes?

[#5]

Only changes when data is refreshed i.e. the most recent invoice data is imported / loaded.

[sunny_talwar]

May be use the Peek function to set value

LOAD Max(InvoiceDate) as MaxInvoiceDate

Resident ....;

LET vMaxInvoiceDate = Peek('MaxInvoiceDate');

and then this

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

[#7]

Load fails when using:

LOAD Max(InvoiceDate) as MaxInvoiceDate

says 'Invalid Invalid expression'

[sunny_talwar]

Did you put the Resident TableName part? Can you show the script you used?

[#9]

fact_table:

LOAD

'Actual' AS DataType,

ActualBranch as Branch,

InvoiceNumber,

Floor(InvoiceDate) as InvoiceDate,

Max(InvoiceDate) as MaxInvoiceDate,

Year(InvoiceDate) AS [Year],

Date(MonthStart([InvoiceDate]),'$(vMonthFormat)') as MonthYear,

Month(InvoiceDate) as [Month],

[Customer Type] as [Labour Type],

[LabourMOT] as [Labour Sales],

[Hours Sold],

Service,

MOT,

VHC,

[Booking Date],

Date(MonthStart([Booking Date]),'$(vMonthFormat)') as BookingMonthYear,

BookedByUser As [Booking By],

ReceivedByUser As [Checked In],

InvoicedUser As [Invoiced By],

ExplainedUser As [Handover By],

[Customer Type] as %LabourTypeKey,

Date(MonthStart([InvoiceDate]),'$(vMonthFormat)') as %DateKey

Resident WorkshopSales_temp;

[sunny_talwar]

Something like this

fact_table:

LOAD

'Actual' AS DataType,

ActualBranch as Branch,

InvoiceNumber,

Floor(InvoiceDate) as InvoiceDate,

Max(InvoiceDate) as MaxInvoiceDate,

Year(InvoiceDate) AS [Year],

Date(MonthStart([InvoiceDate]),'$(vMonthFormat)') as MonthYear,

Month(InvoiceDate) as [Month],

[Customer Type] as [Labour Type],

[LabourMOT] as [Labour Sales],

[Hours Sold],

Service,

MOT,

VHC,

[Booking Date],

Date(MonthStart([Booking Date]),'$(vMonthFormat)') as BookingMonthYear,

BookedByUser As [Booking By],

ReceivedByUser As [Checked In],

InvoicedUser As [Invoiced By],

ExplainedUser As [Handover By],

[Customer Type] as %LabourTypeKey,

Date(MonthStart([InvoiceDate]),'$(vMonthFormat)') as %DateKey

Resident WorkshopSales_temp;

MaxInvoiceDate:

LOAD Max(InvoiceDate) as MaxInvoiceDate

Resident fact_table;

LET vMaxInvoiceDate = Peek('MaxInvoiceDate');

DROP Table fact_table;