Issue in creating Flag on UI

[Anonymous]

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 :

Customer	Loan Types	Type	Amount
xxx	abc	Vol	1000
xxx	xyz	Vol	6433
xxx	rst	Rev	241
yyy	abc	Rev	144
yyy	rst	Vol	3415
zzz	xyz	Vol	4534
zzz	rst	Rev	134

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!

[Oleg_Troyansky]

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

[Anonymous]

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.

[Anil_Babu_Samineni]

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)

[qliksus]

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 ?

[Anonymous]

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!