Accumulating Quantity by Date via Set Analysis

[peter_burgess]

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

[marcus_sommer]

You could try something like this:

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

- Marcus

[peter_burgess]

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

[peter_burgess]

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

[sunny_talwar]

How about this?

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

[peter_burgess]

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

[marcus_sommer]

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

[peter_burgess · Accepted solution]

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

[sunny_talwar]

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

Best,

Sunny