Sum if on Date Island - With AND additional Expression.
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.
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). ...)
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.
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.
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.
