Hello,
relatively new to Qlik Sense but I've had experience with other tools. What I'm looking for is something similar to the SumX Dax expression.
When I use the following calculation my formula for the individual periods are correct (the part at the end is to account for year-end adjustments - 35 days in March) but ultimately the equation is looking to calculate the structural hedge that the business area will be paying which is driven by - Average Balance for that month * Bank Base Rate (0.75% when we actioned this forecast)
(((((Sum({$<Product={"Dummy Flex Hedging Op Bal","Flex Hedging Fixed Leg"}>}[Average Balance]))
*
(Avg(BBR)/100
*-1)
*Sum([Sheet1.DayCount]))/Sum([Sheet1.DaysInYear]))/Sum([Sheet1.DayCount])*Sum([Sheet1.Days])))
Period Amount
10 -£11,575,411.24
11 -£10,647,634.82
12 -£12,633,144.49
Grouped to yearly then gives : £34,881,060.37, when it should in fact be: £34,856,190.55
However when I then group the dimension up to the yearly position the total be different to the sum of the individual months.
The drilldown master item that I'm using is a combination of Year/Period.
I don't fully understand the Aggr function but I thought it would look something like:
Sum(Aggr((((((Sum({$<Product={"Dummy Flex Hedging Op Bal","Flex Hedging Fixed Leg"}>}[Average Balance]))
*
(Avg(BBR)/100
*-1)
*Sum([Sheet1.DayCount]))/Sum([Sheet1.DaysInYear]))/Sum([Sheet1.DayCount])*Sum([Sheet1.Days]))),Period)).
That unfortunately doesn't work and returns a value of 0 for both Period/Year dimensions. If I include Nodistinct into the expression as well the values go crazy.
Any help would be greatly appreciated!!!!!!
On a separate note, are Qlik looking into any capabilities of being able to reference master items in other master items? I don't fully understand how the variables work at this stage.
