topic variables creation in QlikView

variables creation

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

Hi All,

I want to create variables like this.

total_od_recovered_above_90	(Total recovered /total OD) percent is above 90
total_od_recovered_76_90	(Total recovered /total OD) percent is between 76 and 90
total_od_51_75	(Total od/payment tenure) percent is between 51 and 75
total_od_above_90	(Total od/payment tenure) percent is above 90
total_od_recovered_51_75	(Total recovered /total OD) percent is between 51 and 75
total_od_76_90	(Total od/payment tenure) percent is between 76 and 90
total_od_26_50	(Total od/payment tenure) percent is between 26 and 50
total_od_recovered_26_50	(Total recovered /total OD) percent is between 26 and 50
sales_vintage_1	Age of the customer lifecycle is 2
pay_tenure_12	Payment tenure is 12
pay_tenure_24	Payment tenure is 24
total_emi_rlz_26_50	Total (recovered + non od)/payment_tenure percent is between 26 and 50
total_emi_rlz_51_75	Total (recovered + non od)/payment_tenure percent is between 51 and 75

I need help in resolving this.

Please help me here.

Thanks.

Re: variables creation

tharanikannan — Thu, 01 Nov 2018 09:30:01 GMT

Hi ,

Please attach a sample qvw file. Creating variables are easy. But please mention your requirement clearly.

Re: variables creation

Anil_Babu_Samineni — Fri, 02 Nov 2018 03:56:15 GMT

Perhaps this? Make sure, This can be used in Front end. If you want to use or calling in script as field then you need Aggregate which based on your Logic of Business.

LET total_od_recovered_above_90 = '=If(Sum(Recovered/OD)>Num(0.90)*100, Sum(Recovered/OD))';

LET total_od_recovered_76_90 = '=If(Sum(Recovered/OD)>=Num(0.76)*100 and Sum(Recovered/OD)<=Num(0.9)*100, Sum(Recovered/OD))';

LET total_od_51_75 = '=If(Sum(OD/Tenure)>Num(0.51)*100 and If(Sum(Recovered/OD)<Num(0.75)*100, Sum(Recovered/OD))';

LET total_od_above_90 = '=If(Sum(OD/Tenure)>Num(0.90)*100, Sum(Recovered/OD))';

LET total_od_recovered_51_75 = '=If(Sum(Recovered/OD)>Num(0.51)*100 and If(Sum(Recovered/OD)<Num(0.75)*100, Sum(Recovered/OD))';

LET total_od_76_90 = '=If(Sum(OD/Tenure)>Num(0.76)*100 and If(Sum(Recovered/OD)<Num(0.90)*100, Sum(Recovered/OD))';

LET total_od_26_50 = '=If(Sum(OD/Tenure)>Num(0.26)*100 and If(Sum(Recovered/OD)<Num(0.50)*100, Sum(Recovered/OD))';

LET total_od_recovered_26_50 = '=If(Sum(Recovered/OD)>Num(0.26)*100 and If(Sum(Recovered/OD)<Num(0.50)*100, Sum(Recovered/OD))';

LET sales_vintage_1 = '=If(Age(Customer, Today())=2,Age(Customer, Today()))';

LET pay_tenure_12 = '=If(Payment_Tenure=12,Payment_Tenure)';

LET pay_tenure_24 = '=If(Payment_Tenure=24,Payment_Tenure)';

LET total_emi_rlz_26_50 = '=If(Sum((Recovered+NONOD)/Payment_Tenure)>Num(0.26)*100 and If(Sum(Recovered/OD)<Num(0.50)*100, Sum((Recovered+NONOD)/Payment_Tenure))';

LET total_emi_rlz_51_75 = '=If(Sum((Recovered+NONOD)/Payment_Tenure)>Num(0.51)*100 and If(Sum(Recovered/OD)<Num(0.75)*100, Sum((Recovered+NONOD)/Payment_Tenure))';

Re: variables creation

jyothish8807 — Fri, 02 Nov 2018 06:39:38 GMT

Hi Chandra,

You can do it by loading them in excel as well.

refer this:

Set Analysis in Expression (Chart)?

Br,

Re: variables creation

chandra63 — Fri, 02 Nov 2018 11:56:55 GMT

Hi Anil,

Thanks,

First Variable is showing the value.

Rest of the things we are not getting Values

output: '-' like this

please do needful

Thanks

Re: variables creation

Anil_Babu_Samineni — Mon, 05 Nov 2018 14:45:26 GMT

Try this in text object.

If(Sum(Recovered/OD)>=Num(0.76)*100 and Sum(Recovered/OD)<=Num(0.9)*100, Sum(Recovered/OD))

Re: variables creation

chandra63 — Wed, 07 Nov 2018 05:45:22 GMT

HI Tharani,

help me on this.

Variables Creation & Equation

please refer this.

Thanks & Regards.

Chandra

Re: variables creation

chandra63 — Wed, 07 Nov 2018 07:03:06 GMT

Hi Anil,

I have tried it. Still showing no value

Please help me.

Thanks.

Re: variables creation

Anil_Babu_Samineni — Wed, 07 Nov 2018 08:21:59 GMT

looks like, you don't have values between the condition? Would you like to share sample test?

Re: variables creation

chandra63 — Wed, 07 Nov 2018 08:30:58 GMT

HI Anil,

help me on this.

Variables Creation & Equation

please refer this.

Thanks & Regards.

Chandra

Re: variables creation

chandra63 — Thu, 08 Nov 2018 06:57:23 GMT

Hi Anil,

it works.

Thanks.its Helpful to me

Chandra

Re: variables creation

chandra63 — Thu, 08 Nov 2018 09:01:14 GMT

Hi anil,

please help in this calculations.

avg_gap_bet_od_0	Average gap between two OD instances is 0 months
avg_gap_btw_rec_0	Average gap between two recoveries is 0 month
avg_gap_btw_rec_7_12	Average gap between two recoveries instances is between 7 and 12 months
avg_gap_btw_rec_4_6	Average gap between two recoveries instances is between 4 and 6 months
avg_gap_bet_od_rec_1_3	Average gap between OD and its recovery is between 1 and 3 months

I was wondering if I could get some help please.

Thanks in Advance.

Re: variables creation

chandra63 — Wed, 14 Nov 2018 02:51:59 GMT

Hi,

Please help me on the above .

Thanks in Advance.

Chandu