Aging Calculation

[vasathi]

Dear Team

I have and requirement to match the Inventory aging report with one of ERP (AX) reports.

To achieve this, i have written the following queries to build the report and which is fine.

I want to know any other simplify methods to calculate the same.

I need to calculate 0-6 M, 6m-1yr, 1yr-2yr, 2yr-3-yr, 3yr-4yr, 4yr-7yr and 7yr above 

(i know we can create intermidiate table and filter the records).  

To achive this i have created sveral calcuation like following
for Age1 (0m-6m)

Please suggest me how to simplyfy this; due to this calculation i am performance issue also.

[vasathi]

Dear Rico,

Sorry for late response and i'm out of work last few days;
i'll prepare the sample file and share with you

Regards

[Greg_Deckler]

@vasathi Could you provide some example/sample data? Since your calculations are using VAR, just move the VAR statments to the other calculation to merge. Not sure if you would get a performance benefit from that though.

[vasathi]

Dear Greg

Thanks for your immediate response; and i could not able to provide the sample since data is huge;

Actual requirement is
for Age1 (0m-6M) - first Find aging qty then Minimum of aging qty and Actual stock qty 
for Age2 (6m-1Yr) - Find aging qty the Minimum of Age1, Age2, actual stock 
....
in the last Age (7year and above)
Find Ageqty Actual Stock qty - (Age1 stk .... AgeN Stk) then minimum of  Age7, actual Stock

hope you can help me....

[Anonymous · Accepted solution]

Hi @vasathi ,

Do you want to merge all calculations to one or merge find age and find qty to one?

If you want to merge find age and find qty, you can try to create virtual table and add find age as a column then calculate find qty.

If you want to merge all calculations to one,according to your screenshot, I think your calculation logic is complex.

If there is a age table with age range, you can add a switch function to combine thems. If this reply still couldn't help you solve your problem, I suggest you to share a easy sample file with us. And you can show us a screenshot with the result you want. This will make us easier to find the solution. 

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vasathi]

Dear Rico

Sorry for delay;

Still i'm struggling to get the solutions.
I have tried to combine the query as follows but result is not correct;

download sample file : download here 

Regards