topic Using Inter record data in calculation in QlikView

Using Inter record data in calculation

Mon, 22 Nov 2010 16:42:52 GMT

Hi,

I need to calcualte the Available date of a top-assembly based on the Available Date of sub-assemblies and delivery of Buy articles.

I load the delivery dates of Buy articles and the Manufacturing Lead Time from text files.

I then need to calculate the Available Date of the assemblies based on the Bill of Material structure.

I have attached a table that hopefully explain what I'm trying to acheive.

---

Can this be done as an expression in a Straight Table or do I need to Load the data differently?

Please let me know if you have any questions as it's difficult to explain.

Grateful for any help!

Best Regards,
Jonas

Using Inter record data in calculation

renjithpl — Mon, 22 Nov 2010 17:34:52 GMT

Hi,

I can see some yellow rows and Blue rows in buy artical available data. Is there a condition you used to make it yellow and blue. or you just colored it in your excel sheet. Qlikview wont recognize the colour from data source as far as my knowledge. if there is any condition to select those coloured boxes, then you can just let us know.

Regards

Ranjit

Using Inter record data in calculation

Mon, 22 Nov 2010 18:39:26 GMT

Hi Ranjit,

Sorry for the confusion. I only colored the data in Excel to show how I want to calculate the data.

Let me try and explain the data better;

Top-level assembly ABxx1021A4 are built up of two sub-assemblies, ABxx0100A7 and ABxx0100A7 (same assembly but disregard this)

Each sub-assembly are built up with different material (colored yellow and blue in the spreadsheet just for illustration).

I want to calculate an Available Date for each sub-assembly and top-assembly based on the buy articles Available Date and the Mfg. LT of the assembly.

---

Expression for Subassembly line 9 should be something like;

=max(Buy Article Available Date) + Process LT (but only max of data in line 4-8)

Expression for Subassembly line 2 should be something like;

=max(Buy Article Available Date) + Process LT (but only max of data in line 10-15)

How do I take max of a sub-set of data in an expression?

---

Expression for Top-assembly;

=max of calculated Available Date for Subassemblies + Mfg. LT for top-assembly

How do I make an expression on a calculated data?

Alternatively if I can use;

=max(Buy Article Available Date) + Process LT of both sub-assembly and top-assembly

---

Best Regards,

Jonas

So, what I'm trying to do is take the maximum Available Date for a level and a the manufacturing LT. Something like;

=max(Buy Article Available Date) + Process LT

The problem is that I only want to take max of a subset of data. For example, line 9 in the spreadsheet should only be max of line 10-15 as these are level 2 and belong to the above level 1.