Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Issue in creating Flag on UI

Hi Experts,

I am getting issue in creating a Flag on the fly(on UI). I have created a Flag which is aggregated on higher level granularity, but want to use that Flag in lower level dimension chart. I tried to work on variable definition to achieve this, but somehow it is not behaving the way, I am expecting.  Due to security reason, I am not allowed to upload any files.

My sample data :

 

CustomerLoan TypesTypeAmount
xxxabcVol1000
xxxxyzVol6433
xxxrstRev241
yyyabcRev144
yyyrstVol3415
zzzxyzVol4534
zzzrstRev134

My requirement is to show loan volume amount for the loan types which has $(vRatio) less than 3.

Chart Type : Pie Chart

Dimension : Loan Types

Expression : sum({<Type={'Vol'},$(vFlag)={'Y'}>}Amount)

Variable Definition :

vRatio = Aggr((sum({<Type={'Rev'}>}Amount)/sum({<Type={'Vol'}>}Amount)),Customer)

vFlag  = if($(vRatio)<3,'Y','N')

There may be some way to resolve this issue by revising the data model, but I do not want to change my data model. Please advise some way to do it on UI itself.

Thanks!

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Hi,

you are trying to use a calculated formula in a Set Analysis filter in place of a field, which is not permitted. Each filter should look like this:

Field = {values)

You can't filter like this :

IF (... Y, N) = Y = {1} - this is simply an incorrect syntax.

In order to get the result you are looking for, you need to use an Advanced Search in Set Analysis. Something like the following should work:

{<Customer={"=condition"}>}

For example:

{<Customer={"=sum({<Type={'Rev'}>}Amount)/sum({<Type={'Vol'}>}Amount)"}>}

Learn Set Analysis and many more advanced techniques for QlikView and Qlik Sense in my book QlikView Your Business.

Cheers,

Oleg Troyansky

Oleg Troyansky

Masters Summit for Qlik is going virtual! Dec 16th - I'll teach Performance Tuning

Highlighted
Creator
Creator

Thanks for your reply!

I tried to modify the calculation as you said, still am not getting the expected result. The calculation(filter) is getting applied to Loan Types also. That means if  I have put <10 condition, it is checking for Loan Types value which has <10.

I need the calculation(filter) to be pre defined on Customer level(higher) and then applying in loan volume calculation for Loan Type (lower) .

Can we something do on UI instead of changing script?

Thanks.

Highlighted

What is your expected values. Try this and let me know

Variable Definition :

vRatio = Aggr(sum({<Type={'Rev'}>}Amount)/sum({<Type={'Vol'}>}Amount),Customer, [Loan Type])

vFlag  = if('$(vRatio)'<3,'Y','N')


sum({<Type={'Vol'},$('$(vFlag)')={'Y'}>}Amount)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Specialist II
Specialist II

Can you post the expected output in the above table ? bczo you said the below

My requirement is to show loan volume amount for the loan types which has $(vRatio) less than 3.

So do you want the calculation to be based on the loan type irrespective of the customer ?

Highlighted
Creator
Creator

Sorry for late response. These expressions did not help me.

I needed to show loan volume for Loan types(lower granularity) based on flags created on customer(higher granularity).

I may need to change my data model, as it deals with different level granularity.

Thank you all your responses and help!