topic Re: Accumulating Quantity by Date via Set Analysis in QlikView

Accumulating Quantity by Date via Set Analysis

peter_burgess — Fri, 09 Dec 2016 01:11:39 GMT

Hi.

I have two tables, one called "Sales_Orders" and the other called "Production_Orders", related via the Product name.

The Sales_Orders table (simplified) has fields S/O Number, Quantity required, and Due Date.

The Production_Orders table (simplified) has fields JobNumber, QtyToMake, and ExFactoryDate.

Sales_Orders Production_Orders

Product Product

S/O Number JobNumber

Quantity QtyToMake

Due Date ExFactoryDate

Jobs are often manufactured in quantities that are less than a particular Sales Order demand.

In a Straight Table Chart, I am determining whether there is going to be sufficient Product made to meet the Sales demand, by using the following expression: [InTime Job Qty] = sum(If(ExFactoryDate<=Due Date,QtyToMake,0))

This expression works fine, but I would really like to turn it into a Set Analysis expression. This will enable me to be more flexible with other things I want to do with the data later.

But I am having real difficulty in determining what the syntax of the Set Analysis expression should be, and whether I need to use the aggr() function - and if so, how.

I can see that somehow I need to have a set of records where the ExFactoryDate in the Production_Orders table is less than or equal to the Due Date of the record of interest in the Sales_Orders table. But the syntax escapes me and I haven't been able to find a thread yet to lead me on the right track.

Can anyone point me to the answer please?

Many thanks,

Pete

Re: Accumulating Quantity by Date via Set Analysis

marcus_sommer — Fri, 09 Dec 2016 09:15:07 GMT

You could try something like this:

sum({< QtyToMake = {"=ExFactoryDate<=Due Date"}>} QtyToMake)

- Marcus

Re: Accumulating Quantity by Date via Set Analysis

peter_burgess — Sat, 10 Dec 2016 04:49:07 GMT

Thanks Marcus, but the fix doesn't work as hoped.

It's helped me though to better understand what's happening with the data, so hopefully I will be able to formulate a working expression soon.

Regards,

Pete

Re: Accumulating Quantity by Date via Set Analysis

peter_burgess — Mon, 12 Dec 2016 01:20:46 GMT

Update ...

Marcus was able to direct me to writing a syntax-correct expression. The problem is now one that I think may be essentially impossible to solve - at least easily - as a set expression, and therefore I am currently defaulting to using the original expression that works: sum(If(ExFactoryDate<=DueDate,QtyToMake,0)).

The sum({< QtyToMake = {"=ExFactoryDate<=DueDate"}>} QtyToMake) expression works but ONLY when there is just one 'DueDate' per Product. My problem arises when there are two or more rows of Sales Orders for the same Product, but those rows have differing Due Dates. In the set calculation, it appears that QlikView is taking the 'DueDate' value to be the last (since the order is set as ascending, therefore the latest) date. So often, this causes incorrect calculations to be made for the earlier rows.

I can see now that this is probably exactly the way that QV is supposed to work, sets are not really designed to be calculated row by row. I am parking the problem for now, I need to understand a bit more about sets and syntax before (if) I decide to come back to it.

Thanks,

Pete

Re: Accumulating Quantity by Date via Set Analysis

sunny_talwar — Mon, 12 Dec 2016 01:46:29 GMT

How about this?

Sum({<Product = {"=ExFactoryDate<=DueDate"}>} QtyToMake)

Re: Accumulating Quantity by Date via Set Analysis

peter_burgess — Mon, 12 Dec 2016 02:29:52 GMT

Hi Sunny.

Thanks. Your formula is actually more solid than using 'QtyToMake' on the LHS of the set modifier expression. Indeed, when I used Marcus' suggestion first off in my table, I actually substituted 'QtyToMake' with 'JobNumber' to create a better solution.

But regardless, the expressions sum({< JobNumber = {"=ExFactoryDate<=DueDate"}>} QtyToMake) and your suggestion sum({< Product = {"=ExFactoryDate<=DueDate"}>} QtyToMake) both produce the same results which are, as explained in my earlier post of today, occasionally but not always correct.

I believe the crux of my problem boils down to the fact that in the part of the expression that features the set {"=ExFactoryDate<=DueDate"}, DueDate needs to adopt the value of the individual Sales Order row that is being addressed, and I can't seem to figure out how to do that. Or whether it is even possible to do that, when there are multiple lines for the same Product and each line may have a different DueDate.

Cheers,

Pete

Re: Accumulating Quantity by Date via Set Analysis

marcus_sommer — Mon, 12 Dec 2016 06:24:10 GMT

Sometimes you could in such cases include an aggr-function, maybe like this one:

sum({< Product = {"=aggr(ExFactoryDate<=[Due Date], [Due Date])"}>} QtyToMake)

whereby this caused an increase of complexity and I'm not sure if the performance then is always better as by an if-loop - whereby I suggest to transfer the if-loop from the inside to the outside:

If(ExFactoryDate<=DueDate, sum(QtyToMake))

- Marcus

Re: Accumulating Quantity by Date via Set Analysis

peter_burgess — Tue, 13 Dec 2016 01:19:12 GMT

Update ...

Thanks for persevering with suggestions, Marcus.

Unfortunately, neither the aggregated function you put forward, nor the If statement wrapped on the outside, produces the correct results like the original internally-positioned If statement - sum(If(ExFactoryDate<=Due Date,QtyToMake,0)) - did.

However, I'm more comfortable now that I haven't overlooked something obvious. And the original If statement is not affecting performance, my data set simply isn't big enough (nor ever will be) for that.

Regards,

Pete

Re: Accumulating Quantity by Date via Set Analysis

sunny_talwar — Tue, 13 Dec 2016 01:26:24 GMT

Please close this thread by marking your own response as correct.

Best,

Sunny