Hello Peers,
I have a use case as described below in which I am unable to get to the right amount in the analysis. Would like to request your thoughts for a solution on this.
Scenario/Issue: My users are comparing/reconciling the amounts between two systems. On System A, the amounts are calculated based on date (Sum of all amounts until that chosen date) while on System B, the amounts calculated are based on Calender Period (Sum of all amounts until that period chosen e.g. July 2014 (07 2014), August 2014 (08 2014) etc.). And these both systems are supposed to be equal.
While reconciling these amounts, I have noticed that System A falls short of certain amount every time at the end of each month. The reason being, there are few expenses that are made in the first week of each month that are being pushed into previous accounting period such as 'Salaries' for example (July's company salaries are being pushed as expenses for July Accounting Period while Accounting Date remains in August like 8/1 or 8/2 or 8/3 etc.). As a result, the amounts never reconcile. For example below:
|
| 7/31/2014 | 7 | 2014 | 20 |
| 8/1/2014 | 7 | 2014 | 15 |
| 8/2/2014 | 7 | 2014 | 13 |
| 8/3/2014 | 8 | 2014 | 22 |
Required Solution: I would like to know what should my formula look like if I ask a user with a dashboard prompt to select a date and pass that date into a variable (e.g. {date1} is the variable) thereby into the analysis column formula. Please see example below:
In System A, imagine the date is passed into presentation variable as July 31st 2014 into below formula:
FILTER((CASE "Fact"."Amount" WHEN 0 THEN NULL ELSE "Fact"."Amount" END) USING ("Time"."Ac Date" <= @{date1}{date '2099-01-01'}))
--- this would return sum of all amounts until 7/31/2014. For example, the amount it returns from above table would be only 20 $.
While in System B, imagine following period and year is passed i.e. '07 2014' as input and total amount is calculated to be 20+15+13= 48 $. Hence these both systems don't match.
So, how do I rewrite my formula to accommodate Accounting Period or deriving amount using both Accounting Period and Accounting Date into column formula? I appreciate any thoughts on rewriting the formula.
Thanks,
Pradi.