Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This seems to be working
=Sum({<CalDate = {"$(='>=' & Date(Today()) & '<=' & MonthEnd(Today()))"}>}WorkDay)
This seems to be working
=Sum({<CalDate = {"$(='>=' & Date(Today()) & '<=' & MonthEnd(Today()))"}>}WorkDay)
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
This Max(InvoiceDate) changes based on selections or is this static value and you only expect it to change when the application refreshes?
Only changes when data is refreshed i.e. the most recent invoice data is imported / loaded.
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)
Load fails when using:
LOAD Max(InvoiceDate) as MaxInvoiceDate
says 'Invalid Invalid expression'
Did you put the Resident TableName part? Can you show the script you used?
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;
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;