Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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)
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 ?
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!