I made an attempt at switching the primary source to be 2018Holding
and then used a calculated field on the Query datasource of:
IF ATTR([Rekening])='0010' THEN SUM([CURR_BALANCE])
ELSEIF ATTR([2018 - 3+ (Holding)].[Rekening])='0191'
THEN SUM([2018 - 3+ (Holding)].[CURR_BALANCE])
In sheet calcTable it shows the levels of calculation,
and sheet endResult shows the final value after some text filtering.
Please see the workbook v10.5 attached in the Forum Thread.
276764sum.twbx 138.0 KB
First of all, thanks for your support!
I have still one question. Eventually I want to make a parameter to set the date for which the CURR_BALANCES must be shown. So in my workbook you find the MaxDateParameter. If you move 'Jaar' and 'Periode' to the filter shelf in sheet 1 and set it to year=2018 and periode=5, you see that a value of 11.582,39 corresponds to Rekening 0010.
When we do the same in Sheet 2,
We see that a value of 2 from Rekening 0191 falls within period=3, year=2018.
The MaxDateParameter must look if there are values that have an equal or below period/year as the given MaxDateParameter period and year. So when we set the MaxDateParameter to
in endResult, CustomCalc should add 11.582,39 (0010, in period 5, 2018) to 2 (0191, in period 3, 2018), since period 5, 2018 and period 3, 2018 are both equal or below the period/year in MaxDateParameter. Suppose that we changed MaxDateParameter from 05/2018 to 04/2018 then CustomCalcSum should show 7.201,89, since 7.199,89 is the first value below 04/2018 that exists in period 10, 2017 (see sheet1 below):
However the 2 should still be added as this value is from period 3, 2018 as shown above (this explains 7.201,89 (7.199,89+2)) . If no value could be found a 0 should be returned. So when, we change the MaxDateParameter to 02/2018, a 0 should be added to 7.199,89, since there is no value earlier than 03/2018 in sheet 2.
How can I achieve this?
I attached the workbook in this post (see sheet endResult(2) in the attached workbook). The parameter is working now for 0010 but not for 0191 (note that the addition has not yet been taken into account. e.g. 7.199,89 + 2 = 7.201,89):
Fixed it , please check the attached workbook.
276764sum (1).twbx 138.6 KB
I'll have to admit that I'm not sure if I'm following all that has transpired.
But in the end, if it is working for you, I'm glad if I was able to nudge along the way.
I'm also not clear on how your datasources interact, and am wondering
if there is not a more direct way to join 0010 and 0191.
Possibly some setup in Tableau Prep could assist.