Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
quilldew
Creator
Creator

Removing Sum(if()) (because it's slow as hell)

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Perhaps you're looking for something like this: Creating Reference Dates for Intervals


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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?


talk is cheap, supply exceeds demand
quilldew
Creator
Creator
Author

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?

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Perhaps you're looking for something like this: Creating Reference Dates for Intervals


talk is cheap, supply exceeds demand
quilldew
Creator
Creator
Author

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.

quilldew
Creator
Creator
Author

That was a real big help and information I can use for other projects in future thank you.