How to reference a field in the current row of a straight table in a set analysis.

[SBDataspark]

Hi there,

I'm trying to reference a field in the current / same row of a straight table in a set analysis.

To make this easy to understand I have two tables:
- Planned purchase orders (ItemID, PlannedDate, Quantity)
- Supplier items with lead time (ItemID, ItemCode, ItemDescription, Supplier, Lead time). Multiple suppliers per item are possible, but for now that's not an issue.

Now I'm trying to calculate in a straight table the quantity to be received within the lead time of the supplier.

Straigth table has the following columns:
- ItemCode
- ItemDescription
- Supplier
- Lead time
- Total in order -> Sum([Planned Quantity])
- In order between today and lead time date -> I can't get this to work

I've tried several things.

Sum({<PlannedDate={"<=$(=Date(Today()+[Lead time]))"}>} [Planned Quantity]) doesn't work.

It seems like [Lead time] refers to all values of [Lead time] in the table, because after experimenting with Min() and Max() based on other topics on the community it shows a date in the expression. Not the date I want, but at least a date.

Sum({<PlannedDate={""<=$(=Date(Today()+Min([Lead time])))"}>} [Planned Quantity])

But I want to use [Lead time] of the current row of the table.

Any ideas?
Thanks in advance!

Kind regards,

Sebb.

[rwunderlich · Accepted solution]

Set Analysis can't vary by chart row, because the set is computed only once, before the chart is calculated. If you want to do a row-by-row thing, you have to use If(). 

Sum(If(PlannedDate <= Today()+[Lead time], [Planned Quantity])) 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[SBDataspark]

Thank you for your explanation Rob. Really appreciate it!!

The "PlannedDate" isn't a dimension/measurement/column/value in the straight table that I'm creating.
But I see in the result of your expression that I can do an if statement on the value anyway.
That's good to know...😉

As always this was a bit of simplification of the problem I was facing.

In the straight table I can have two suppliers for the same item.
Supplier 1 and Supplier 2.
Now I only want to show the [Planned Quantity] next to Supplier 1 that I've ordered the products from.

With the expression Sum(If(PlannedDate <= Today()+[Lead time], [Planned Quantity])) it shows the amount next to both suppliers. Which is correct based on the contents of the expression.

I've tried with a couple of aggr sum expressions, but can't get the right result.

Aggr(Sum(If([PlannedDate] <= Today() + [Leadt time], [Planned Quantity])), ItemCode, ItemDescription, Supplier)
still shows the quantity for both suppliers.

Aggr(Aggr(Sum(If([PlannedDate] <= Today() + [Leadt time], [Planned Quantity])), ItemCode, ItemDescription), Supplier)
shows me the quantity x 2 in the column of the right supplier.

But I don't want to divide by 2, just for the sake of it. I'd like to show it correctly with the right expression...😀

When I was creating this post I fiddled some more and ended up with the following expression.

Aggr(Aggr(Sum(If([PlannedDate] <= Today() + [Leadt time], [Planned Quantity])), ItemCode, ItemDescription, Supplier), [Planned Quantity])

As you can see this gives me the right results. I don't know exactly why...🤣

Would you be so kind as to maybe explain why this works?

Thanks again!!

[SBDataspark]

Oh, by the way this expression only works when I make a selection on Items and it doesn't work without a selection.
Need to fiddle some more to get lucky to get it to work when there's no selection made...😀

[SBDataspark]

In the end I had to change the data model to suite some other requirements.
While loading the stock planning lines I added the following in the load script:

If(PlannedDate <= (Today() + [LeadTime]), 1, 0) as [WithinLeadTimeFlag]

And created the following set expression:

Sum({$<[Planningtype]={'PurchaseOrder'}, WithinLeadTimeFlag={1}>} [Planned amount])