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]

Yes I have tried, but It doesn't work. 
"

if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <=30 ,$(vPrincipal),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <=60, $(vPrincipal2),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <= 90, $(vPrincipal3)),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id)<= 180, $(vPrincipal4),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id)<= 365, $(vPrincipal5), $(vPrincipal6)
          ))))

"

[teiswamsler]

try this

Max(
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <=30 ,$(vPrincipal),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <=60, $(vPrincipal2),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id) <= 90, $(vPrincipal3)),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id)<= 180, $(vPrincipal4),
if(aggr(max(if(daysprincipal-pdsum>0.01,ReportDate-daysdate)),loan_app_id)<= 365, $(vPrincipal5), $(vPrincipal6)
))))
)

[tomovangel]

The syntax is correct, but the returned values are still -, I keep thinking that there should be a way to reference the value inside.. but I guess there is not such a function in the userinterface... 

[teiswamsler]

I have manage to get it working with on my server.

You can refer back to measures by name or column() but not calculated dimension.

Another solution chould be to use a synthetic dimension but then it is not possible to use aggr()

[tomovangel]

Well, I have to use aggr, because for each loan_app_id, I have multiple daysdate, and thus i have to get the max out of these aggregated by each loan. 

And this fields must stay as a dimension in the table..

I have tried almost anything. 

I got it working in qlikview but the function I used there is not supported in Qlik Sense....

Thank you for your assistance, but i will still wait a few days maybe someone will come with an answer, or its not possible in this environment. 

My last resort is to have a field in the table with numbers (ex IndexField=1,2,3,4,5,6) and then reference this index field, but this will add a column, that my client doesn't want to see.. 

[teiswamsler]

Hi @tomovangel

Have you solved this yet?

Br
Teis

[tomovangel]

No, 

I am thinking of using a field with numbers from 1 to 8, so it will always show the full calculated field, and then base the variables on this field.. but this is not a good way, because I will have 1 extra column with this index field... 

[teiswamsler]

Could it be a solution using the index and condition all the measures on resault on the first field. Then in properties "add on" remove zero values?

[tomovangel]

I will look for a way, to display these variables based purely on the Aggregated' Dimension's values, until I have to publish this report, and If i don't find nothing until then i will just use index field....