topic Sum expression between two dates in QlikView

Sum expression between two dates

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Sum expression between two dates

sunny_talwar — Mon, 13 Mar 2017 15:24:46 GMT

This seems to be working

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

Re: Sum expression between two dates

Mon, 13 Mar 2017 15:56:30 GMT

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

Re: Sum expression between two dates

sunny_talwar — Mon, 13 Mar 2017 15:58:24 GMT

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

Re: Sum expression between two dates

Mon, 13 Mar 2017 16:02:14 GMT

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

Re: Sum expression between two dates

sunny_talwar — Mon, 13 Mar 2017 16:09:01 GMT

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)

Re: Sum expression between two dates

Mon, 13 Mar 2017 16:26:24 GMT

Load fails when using:

LOAD Max(InvoiceDate) as MaxInvoiceDate

says 'Invalid Invalid expression'

Re: Sum expression between two dates

sunny_talwar — Mon, 13 Mar 2017 16:28:18 GMT

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

Re: Sum expression between two dates

Mon, 13 Mar 2017 16:57:52 GMT

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;

Re: Sum expression between two dates

sunny_talwar — Mon, 13 Mar 2017 17:00:12 GMT

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;

Re: Sum expression between two dates

Mon, 13 Mar 2017 17:52:35 GMT

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.

Re: Sum expression between two dates

sunny_talwar — Mon, 13 Mar 2017 18:00:14 GMT

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)

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

Re: Sum expression between two dates

Mon, 13 Mar 2017 18:26:20 GMT

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.

Re: Sum expression between two dates

sunny_talwar — Mon, 13 Mar 2017 18:27:44 GMT

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

Re: Sum expression between two dates

Tue, 14 Mar 2017 12:40:44 GMT

Only when new data is loaded and the vMaxInvoiceDate changes