Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

reference a value from inside a calculated dimension

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. 

Screenshot_6.jpg

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. 

Screenshot_8.jpg
How to achieve this ?

Note: I have tried using ROWNO, GetFieldSelections, as well as different things, but nothing has satisfied the requirement..

18 Replies
Partner
Partner

Anyone

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

 

Partner
Partner

Re: reference a value from inside a calculated dimension

Anybody?

Partner
Partner

Re: reference a value from inside a calculated dimension

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

Partner
Partner

Re: reference a value from inside a calculated dimension

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.Screenshot_14.jpgSo I need something which connects to the Group aggregated dimension and assigns the value, based on the bucket.

Highlighted
Partner
Partner

Re: reference a value from inside a calculated dimension

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)

)))))

 

 

Partner
Partner

Re: reference a value from inside a calculated dimension

Screenshot_15.jpgScreenshot_16.jpg



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

Partner
Partner

Re: reference a value from inside a calculated dimension

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

Re: reference a value from inside a calculated dimension

Nothing, it returns (-) null values. 

Partner
Partner

Re: reference a value from inside a calculated dimension

hmm.

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