Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum expression between two dates

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.

1 Solution

Accepted Solutions
sunny_talwar

This seems to be working

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


Capture.PNG

View solution in original post

14 Replies
sunny_talwar

This seems to be working

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


Capture.PNG

Not applicable
Author

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?

Not applicable
Author

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)

Not applicable
Author

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?

Not applicable
Author

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;