There is this data of govt Incentives, that a company gets for opening factories in remote locations. An incentive passes through many departments of the Govt offices before the money is released to the company. They are tracking this data at every stage. The sample data of one of the incentive of this scenario has been attached in this post. The task is to check Outstanding amount as on a date that a user selects on front end.
Note:-
If an incentive is at maximum stage, that amount only is considered for that incentive and not the amounts in the previous stages.
The stages are named as statuses and have numerical values assigned to them. from 10 to 60. 60 being the final stage after which the amount is released by govt and is denoted as negative amount.
The status has its status date which gives information as to when each status change occurs for the incentive.
Each unique incentive is identified using unique code field which is common for Incentive through all its stages.
The expression I have written is only able to fetch 1 line of the max stage, where as I need to consider all the lines in that stage.
Expression used to calculate Outstanding of such data:-
(v_ASDT) is a variable containing as on date.
=
(sum({<Stage={10},[Status_Date]={"<= $(=num(v_ASDT))"}>}if( aggr(max({<[Status_Date]={"<= $(=num(v_ASDT))"}>}Stage),[Unique Code])=10 , Amount))/100000)
+
(sum({<Stage={20},[Status_Date]={"<= $(=num(v_ASDT))"}>}if( aggr(max({<[Status_Date]={"<= $(=num(v_ASDT))"}>}Stage),[Unique Code])=20 , Amount))/100000)
+
(sum({<Stage={30},[Status_Date]={"<= $(=num(v_ASDT))"}>}if( aggr(max({<[Status_Date]={"<= $(=num(v_ASDT))"}>}Stage),[Unique Code])=30 , Amount))/100000)
+
(sum({<Stage={40},[Status_Date]={"<= $(=num(v_ASDT))"}>}if( aggr(max({<[Status_Date]={"<= $(=num(v_ASDT))"}>}Stage),[Unique Code])=40 , Amount))/100000)
+
(sum({<Stage={50},[Status_Date]={"<= $(=num(v_ASDT))"}>}if( aggr(max({<[Status_Date]={"<= $(=num(v_ASDT))"}>}Stage),[Unique Code])=50 , Amount))/100000)
+
(sum({<Stage={60},[Status_Date]={"<= $(=num(v_ASDT))"}>}if( aggr(max({<[Status_Date]={"<= $(=num(v_ASDT))"}>}Stage),[Unique Code])=60 , Amount))/100000)
+
(sum({<[Status_Date]={"<= $(=num(v_ASDT))"},Stage={1}>} Amount )/100000)
Please help.
