reference a value from inside a calculated dimension

[tomovangel]

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..

[tomovangel]

If anybody has done something like this in Qlik Sense( Not Qlikview, because, there is a function there (getcurrentfield), that will work, but it doesn't exist in QS..) please help..

[tomovangel]

Anybody?

[teiswamsler]

Hi @tomovangel

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 )

?

Br.

Teis

[tomovangel]

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.

[teiswamsler]

Hi @tomovangel

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)

)))))

[tomovangel]

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...

[teiswamsler]

What happens if you change 0 to 'Provision 0 - 30 days' for the first instance in the if statement?

[tomovangel]

Nothing, it returns (-) null values. 

[teiswamsler]

hmm.

Have you tryid reusing the calculated dimention as a measure, and then insert variables as a values?