Forum Discussion
Helper IVFulfill null values with zeros based in a condition
The customer can order some personalized products (IDNR = product number). When he does that, we agree to a minimum quantity he needs to order every year (Jahresmenge lt. ENK). I would like to visualize if the customer fulfill the agreement or not. The actual quantity he ordered (Menge) and how much it is missing to reach the annual quota.
The problem is that when he does not order in one specific year, I don't have a zero as ordered quantity in this year. Therefore, I can not calculate how much it is missing for reach the quota.
In the picture is an example of what I would like to achieve.
I only wants a "0" in Menge in the Business Years that the product exists.
So, for product 06678984, it should show exact this because in 2018/19 the product still did not existed. For this year, the field should be null. The product was created in 27.10.19 and this is business year 2019/20.
However, product 0777898 was created in BY 2018/2019. So, for BY 2021/22 it should show a 0 in Menge. The customer has not ordered anything yet. He still needs to order 5000 for reach the quota.
Can you help me to do this?
There is a dummy dataset in google drive.
https://drive.google.com/drive/folders/1waRsOLMWQIhyJasxtPSCt2bYq_9Ucv_z?usp=sharing
PS: Business Year starts from 01.07/xxxx and goes until 01.06/(xxxx +1)
ex) product created in 27.10.2018 is Business Year 2018/19
Thanks in advance!
7 Replies
- lbendlin
Super User
Your data model is missing a calendar table. Without that you cannot figure out which fiscal year the product was introduced in. What should happen when the product was introduced on the last day of the fiscal year? Shouldn't you prorate the quota for that?
jessicarochalbendlin I do have a calendar table in the model. It is connected by the fiscal year and not the creation date.
However, the data I get is already aggreagated. It shows me the customer's order per business year.
For example, like product 0003 from my table bellow, the product is created in 01.08.2018, therefore, fiscal year 2018/2019. If the customer does not order anything in 2019/2020, this entry does not appear with a 0 and it should...
I also thought about making a cartesian product and make sure that all products have all fiscal years, then replace null with zeros. This approach does not work because it would appear fiscal year before the product was actually created and that is how I don't know how to proceed...Current Table:
Material (fk) Creation Date Order Qty Target Business Year (fk) 0001 01.08.2019 100 150 2019/2020 0001 01.08.2019 150 150 2020/2021 0001 01.08.2019 300 150 2021/2022 0002 01.09.2020 590 500 2020/2021 0003 01.08.2018 900 1000 2018/2019 0003 01.08.2018 1300 1000 2020/2021
Want to add (what is in bold):Material Creation Date Order Qty Target Business Year 0001 01.08.2019 100 150 2019/2020 0001 01.08.2019 150 150 2020/2021 0001 01.08.2019 300 150 2021/2022 0002 01.09.2020 590 500 2020/2021 0002 01.09.2020 0 500 2021/2022 0003 01.08.2018 900 1000 2018/2019 0003 01.08.2018 0 1000 2019/2020 0003 01.08.2018 1300 1000 2020/2021 0003 01.08.2018 0 1000 2021/2022
I am doing all of that because the end goal is to see if the customer reached the agreed target. Like the visualization in my original question.
Thanks again for taking the time to help me out!- lbendlin
Your calendar table needs to have day level granularity to be usable. The fiscal year column is an important part but it cannot be used for the linking . The link needs to be on the date
