Hello, dear Qlikers.
I have been working on the following problem.
I have an aggregated dimension which groups loan applications based on their days in delay:
"= if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <=30 , Dual('Provision 0 - 30 days',0),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <=60, Dual('Provision 31 - 60 days',31),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <= 90, Dual('Provision 61 - 90 days',61),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id)<= 180, Dual('Provision 91 - 180 days',91),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id)<= 365, Dual('Provision 181 - 365 days', 181),Dual('Provision over 365 Days',366))
ReportDate is a field that the user selects from the interface, and it can be different each time. daysprincipal and pdsum, are the amount to be paid and amountpaid, from which i get the first value, when the amount to be paid is not paid in full.
The aggregation works ( i don't know if its correct yet) .
But now a second problem has came up.
I need to reference each of the calculated dimension's fields (Provision 0-30 days) for example, and update them from a variable.
I have used Input box, to store different variables, but now I don't know how I can say where each variable should go.
This is a screenshot from the currently used system of my client. They update each aggregated dimension, by an amount that the user decides.
So in my table I have to have next to each Provision Group, the coefficient, that this group is being summed up by.
This is a screenshot from my QlikSense.
How to achieve this ?
Note: I have tried using ROWNO, GetFieldSelections, as well as different things, but nothing has satisfied the requirement..
I dont fully understand the expected output.
So maybe this is a long short, but cant you use a combination pick() and Rowno()
Pick( Rowno(), v1, v2, v3, v4 )
So, I have a calculated dimension, which is called Group, and basically it stores different loans into aging categories, based on their delay days.
So, depending on the user selection I can have in my dimension all 6 buckets, or I can have only 4 buckets, which is the case in the screenshot below. Your formula works if all buckets are visualized, but if a bucket is not visualized, it takes the corresponding rowno number.So I need something which connects to the Group aggregated dimension and assigns the value, based on the bucket.
I get it.
It is possible to refer back to formerly calulated field by name  (syntax will make and error but calculate)
If( [GROUP] = 0, $(v1)
,If( [GROUP] = 31, $(v2)
,If( [GROUP] = 61, $(v3)
,If( [GROUP] = 91, $(v4)
,If( [GROUP] = 181, $(v5)
,If( [GROUP] = 365, $(v6)
The Syntax is correct, but it doesn't return the values,
You can check my aggregated dimension in the post above, I have stored it in Master dimensions under the name GROUP...