topic Re: Sum if on Date Island - With AND additional Expression. in QlikView

Sum if on Date Island - With AND additional Expression.

Fri, 20 Mar 2015 16:58:52 GMT

Hello,

I'm trying to get the the following statement correct. I have the first version which work ok , but when I try to add a second part to the if statement it calls it invalid.

Syntax that is OK.

sum(if([Delivered2] = [Date], (NewUnits*TimePer)/60))

Syntax that is an invalid Expression.

sum(if([Delivered2] = [Date] and sum(Capacity)-sum((NewUnits*TimePer)/60)<0, (NewUnits*TimePer)/60))

So if I add the underlined element it calls it an invalid expression?

Any help thankfully received.

Regards

Steve

Re: Sum if on Date Island - With AND additional Expression.

Gysbert_Wassenaar — Fri, 20 Mar 2015 17:15:27 GMT

sum(if( ... sum(...) ...)). You're nesting sums. Over which dimensions should sum(Capacity) be calculated? You should use the aggr function to specify these dimensions: sum(if( ... and aggr(sum(Capacity),MyDim1,MyDim2)) ...<0). ...)

Re: Sum if on Date Island - With AND additional Expression.

Oleg_Troyansky — Fri, 20 Mar 2015 17:54:25 GMT

Let me add that this formula could only work on a tiny data set. It will be extremely slow even on mid-size data sets. On a large data set, it will take forever to get calculated.

Keep in mind that when you use a data island in a calculation like this one, QlikView will have to build a Cartesian Join between your Fact table and the Date island, i.e. link all transactions to all dates. Just to visualize the problem - if your Fact table contains a modest 1 mil. rows, and your Date table covers 3 years (approx. 1,000 dates), the Cartesian Join table will contain 1 Billion rows.

I'd recommend to look for a better data model than using Date Islands.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Re: Sum if on Date Island - With AND additional Expression.

Fri, 20 Mar 2015 18:10:34 GMT

Hi Oleg,

Thanks for the info, very useful.

As a planning tools for production off the back of sales orders, the application has an avg 11k lines and we only look forward for 40 days maximum to view the capacity of the factory.

The key to date island is display days when there is no production which has given me the biggest headache.

So any further help would be great.

Regards,

Steve

Re: Sum if on Date Island - With AND additional Expression.

Oleg_Troyansky — Fri, 20 Mar 2015 21:45:04 GMT

Hi Steve,

with such a small volume of data, just about anything would fly.

Yet, you can solve the problem of showing dates when there is no production can be solved with the traditional Master Calendar table that is linked to your Production Dates. Just make sure to include all the dates between the first Production Date and the last Production Date in the Master Calendar, and then you can easily show dates with or without production activity.

cheers,

Oleg