Date Link/Canonical date with different keys and no date field

[Solangeg]

Hi there,

I'm new to Qlik and I've done all the trainings I could, read all the forums/questions and solutions, downloaded the solutions and tried to implement into my app, unsuccessfully. So, I would really appreciate if somebody can help me. Please don't point to the canonical date posts, I've tried it and I can't make it work 😞

The model seems really simple and yet I can't figure it out...

Here is the issue:

I have 3 tables:

Parts: contains the consumption of support parts per month of all parts. Each part has an ID (Support Part)

In this table, each support part (ID) repeats for every month we have data. For example:

Note: day is always 01 (Jan)

Support part | PartDate (YYYY/MM/DD)|Qty

1234| 2022/01/01| 2

1234| 2022/02/01| 1

1234| 2022/03/01| 5

5678| 2022/01/01| 10

5678| 2022/02/01| 0

5678| 2022/03/01| 1

DMR: has the relationship between parts and products. Each spare part is used in one or more products

Commercial Product is the ID of the products

Support Part is the same ID as the previous table. Other fields are not relevant for the issue.

A support part ID will be repeated if it's used in more than one product

Support part | Commercial Product

1234| Product A

1234| Product B

5678|Product C

5678|product A

Finally, the table Products tells how many products we shipped per month, like with the parts, each product ID will be repeated as many months as we have the information:

Commercial Product| TRLDate| QTY Product

Product A | 2022/01/01|150

Product A | 2022/02/01|100

Product A | 2022/03/01|10

Product B | 2022/01/01|350

Product B | 2022/02/01|3500

Product B | 2022/03/01|20

Product C...

For every part, every month, I need to calculate how many parts where used to repair XX amount of products (installed base).

So for Jan-2022, we sent 2 of Part 1234 which is used in Product A and B (Jan amount=150+350)

-> Jan failure rate part 1234=2/500*100

My issue is when I try to plot in the same chart the amount of parts sent per month and the Installed base of the products in which this part is used (different dates). I tried with link date table and canonical date but since both tables that have dates don't have an ID/key in common, dates are repeated as many parts and products I have and parts and they are linked by a table that doesn't have a date field i can't make it work.

I have all the YearMonths since I have data, so I don't worry about a master calendar and I don't mind if I have only one date to select, I don't need to select different dates in both tables.

I hope I explained it correctly. To upload the tables I would need to change the data, I only have the real files and I can't disclose that information.

Thanks in advance for your help!!!!!

[vinieme12]

parts:
Load Supportpart,Qty,date#(PartDate,'YYYY/MM/DD') as part_date , Supportpart&'-'&date#(PartDate,'YYYY/MM/DD') as keyfield
Inline [
Supportpart , PartDate,Qty
1234, 2022/01/01, 2
1234, 2022/02/01, 1
1234, 2022/03/01, 5
5678, 2022/01/01, 10
5678, 2022/02/01, 0
5678, 2022/03/01, 1
];

temp_products:
Load CommercialProduct,Qty_Product, date#(TRLDate,'YYYY/MM/DD') as trl_date
Inline [
CommercialProduct, TRLDate, Qty_Product
Product A , 2022/01/01,150
Product A , 2022/02/01,100
Product A , 2022/03/01,10
Product B , 2022/01/01,350
Product B , 2022/02/01,3500
Product B , 2022/03/01,20
];

left Join(temp_products)
//join dmr table to products as it is a many to one relation on commericalproduct
Load *
Inline [
Supportpart,CommercialProduct
1234, Product A
1234, Product B
5678,Product C
5678,product A
];

products:
Load
CommercialProduct
,trl_date
,Qty_Product
,Supportpart&'-'&trl_date as keyfield
Resident temp_products;
drop table temp_products;

NoConcatenate
Datelink:
Load part_date as date,keyfield,'part_date' as datetype
Resident parts;
Concatenate (Datelink)
Load trl_date as date,keyfield,'trl_date' as datetype
Resident products;

exit Script;

Output

Result

Data Model

Data Model

[Solangeg]

Hello Vineeth,

thank you very much for your help. Sorry for the delay in replying, I was trying to make it work before asking for more help so here I am:

let me explain what my goal is, with the model I need to ultimately calculate and present in bar charts or combo charts the Annual Failure Rate of each part (AFR).

AFR for month m=(Qty_Parts used in month m/IB for month m)*12*100

where IB (Installed base) for month m= Sum of the sold products (Qty_Product) in the last 10 years until month m where the part is used.

For example, for Part 1234, the IB for Feb.= 150+100+350+3500=4100

AFR Part 1234, Feb=(1/4100)12*100=0.29%

The AFR should be aggregated(sum or averaged) in visualizations if more than one part is selected but the IB is always the same for each part, independently of the selections made in the Products dimensions. This is because if I select a Product, let's say product A, I would like to see the monthly Average of the AFR of all it's parts. Since I don't know for which products the ordered parts where actually used, even if Product A is selected, first I need to calculate the AFR according to the formula above, considering all the products where the parts of Product A are used (not only product A) to get the right IB and then their individual monthly AFR to finally make their monthly average.

One issue that I have now with this data model is that the table Products has data since the time each product was launched, some since 1999, but table Parts has only data since Jan 2016, so with the join is only allowing me to calculate the IB with data since both tables have data, that is Jan 2016. Therefore the IB for Jan 2016 is being calculated as the Qty_Product of that month because there is no match before but it should be Feb-2006 to Jan-2016.

 I had the following formula before for the IB= RangeSum( Above(TOTAL sum({<CommercialProduct>} QtyProduct),0,120)) which I'm not totally sure if it was working in all scenarios (selections) but now it's only considering Qty_Products since 2016.

Before linking the tables with the DateLink, the issue I had was that when trying to calculate AFR=(Qty_Parts/IB)*12*100, these two dimensions where referring to two different dates so I couldn't simply calculate it that way.

Sorry for the length of the explanation. I really appreciate your help.

Kind regards,

Sol