topic Issue in creating Flag on UI in QlikView

Issue in creating Flag on UI

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Issue in creating Flag on UI

Oleg_Troyansky — Wed, 12 Oct 2016 18:37:28 GMT

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

Re: Issue in creating Flag on UI

Anonymous — Wed, 12 Oct 2016 19:20:22 GMT

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.

Re: Issue in creating Flag on UI

Anil_Babu_Samineni — Thu, 13 Oct 2016 01:31:25 GMT

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)

Re: Issue in creating Flag on UI

qliksus — Thu, 13 Oct 2016 04:45:55 GMT

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 ?

Re: Issue in creating Flag on UI

Anonymous — Tue, 25 Oct 2016 17:39:21 GMT

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!