Calculation view with time dimension, certain data record missing and not appear

[#1]

Hi All,

I've created a calculation view and join with time dimension. However, certain record did not appear when I preview it. The problem seems happen at 'SalesView_1' join node because when I tried to view data, some of data is missing and not appeared. At 'Sales View' join node, I checked no data is missing. My graphical view as below:

For 'SalesView_1' join, I joined AUDAT(Doc Date) and EDATU (Delivery Date) with Date in FiscalCal to get the fiscal year and period for both of dates. I did a test to join only one field, AUDAT/ EDATU and the data appeared.

Anyone can give me an idea how to solve this problem? Appreciate your help since I dont have any idea how to solve this issue. Thank you.

[KonradZaleski · Accepted solution]

In single join node you should not join both columns (AUDAT,EDATU) to with FiscalCal projection.

In current scenario you will get results only if AUDAT is equal to EDATU. Other records will be filtered out.

You should create two separate join nodes - one between AUDAT and DATE, and the next one between EDATU and DATE. So add another join on top of SalesView_1 and link it again to FiscalCal node. Link only single column in each join node.

[Former Member · Accepted solution]

Hi konrad,

Thank you for your help. Your suggestion seems to work fine and all records displayed. However, there are 2 columns for fiscal year and fiscal period. It is possible for me to only have 1 column for fiscal year and fiscal period? Or maybe instead of using join, I should use union to replace the 'Join_1' node? Need your advice on this.

Please see below screenshot:

This is because the created model will be used in my dashboard. If I have 4 columns (2 columns of fiscal year and 2 columns of fiscal period), it will be difficult for me to assign these columns as filter selection in my dashboard. In addition, it is not the appropriate approach for dashboard to have same attributes as filter selections.

Thank you again.

[KonradZaleski · Accepted solution]

These are two different fiscal years. One for AUDAT second for EDATU. So if you want to have them in single column then union is the right approach. Consider that if there will be different fiscal year for EDATU and AUDAT, then for specific key record, you will have two entries (for example one entry will show Fiscal year =2017, second with Fiscal year =2018).

--If this answers your question, please mark the answer as accepted.

[Former Member · Accepted solution]

Thank you again konrad for your advice. Your solutions is quite good but the first one is more suitable for my scenario. This is because when I'm using union, it only take the AUDAT field and I don't think union is the right approach to use. Btw, thank you again for your solution and advice.