Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Gang,
This one may be a bit confusing so please bear with me.
I have a script, that works, but is slow slow slow and replicated over many straight tables.
I'm looking for a way to move the sums to the load script, but I'm also open to suggestions as to how to speed these up. I can't post a qvw here, because of the sensitive nature of the data, but I'll put up the code I can.
So one of the sums looks like:
=sum(
if($(vRealDateDelayStart)<=floor(WeekEnd(WeekStartDay))
,if($(vRealDateDelayEnd) >= WeekStartDay
,if($(vRealDateDelayEnd) <= floor(WeekEnd(WeekStartDay))
,if($(vRealDateDelayStart) >= WeekStartDay
,($(vRealDateDelayEnd) - $(vRealDateDelayStart)) +1
,($(vRealDateDelayEnd) - WeekStartDay) +1
)
,if($(vRealDateDelayStart) >= WeekStartDay
,(floor(WeekEnd(WeekStartDay)) - $(vRealDateDelayStart)) +1
,(floor(WeekEnd(WeekStartDay)) - WeekStartDay) +1
)
)
,if(isnull($(vRealDateDelayEnd)) and $(vRealDateDelayStart) <= floor(WeekEnd(WeekStartDay))
,if($(vRealDateDelayStart) < WeekStartDay
,(floor(WeekEnd(WeekStartDay)) - WeekStartDay) +1
,(floor(WeekEnd(WeekStartDay)) - $(vRealDateDelayStart)) +1
)
)
)
)
)
The variables are start and end dates created from a timestamp.
I'm sure there will be lots of questions. Hit me.
Perhaps you're looking for something like this: Creating Reference Dates for Intervals
Looks like you're only comparing stuff with WeekStartDay. It shouldn't be too hard to move those comparisons to the script and create a new field to flag the matching records. What timestamp are the variables created from? From value selected in a field or user input or a value like Today's date?
They are created from two timestamps that are pulled in from a qvd and are linked to a 'person' (unique identifier).
I use this expression to show a figure for every week going back to 2012 in a straight table, the weekstart days are currently stored in a mastercalendar on their own.
I also have another straight table, with the same expression, that shows the 'person''s with a period between the vRealDateDelayStart and vRealDateDelayEnd for whichever week is chosen within the first table. Confusing?
Perhaps you're looking for something like this: Creating Reference Dates for Intervals
That does look like it could help. I will take a good look at it.
I've tried moving my above code to the script, and creating a new table using group by, for some reason it is giving me an error that it is expecting a ')' , but when checking the code all the '()' are paired.
That was a real big help and information I can use for other projects in future thank you.