topic Re: Regarding Avg in QlikView

Regarding Avg

d_koti24 — Wed, 15 Jul 2015 10:13:18 GMT

Hi All,

I Have 3 fields like field1,field2,and field3.

these 3 filelds i am using as filter in multibox.

three fields contains the same data

field1: filed2 field3

0 0 0

5 5 5

10 10 10

15 15 15

20 20 20

25 25 25

100 100 100

my requirement is i am able to select only the sum(3 fields) is 100.not greater than 100,

for ex:

suppose if i select 60 in field1 ,i am able to select only 40 in remaining two fields,

if i select 60 in field1 ,25 in field2,i am able to select only 15 in field3 ,

how to achive this?

regards,

Re: Regarding Avg

swuehl — Wed, 15 Jul 2015 10:28:06 GMT

Maybe like attached?

Re: Regarding Avg

marcus_sommer — Wed, 15 Jul 2015 10:42:34 GMT

I think you could create such conditions if you used a filter-expression for the listboxes like:

if(100 - rangesum(sum(Field2), sum(Field3)) <= [Field1 cummulated], Field1)

whereas [Field1 cummulated] need to be created within the script in a sorted load:

cummulated:

Load Field1, alt(Field1 + peek('Field1 cummulated'), 0) as [Field1 cummulated] Resident xyz order by Field1;

This would be needed for each field and each listbox-expression in a similar way.

What is the reason for this tripple field - maybe there are easier ways to reach your goals.

- Marcus

Re: Regarding Avg

d_koti24 — Wed, 15 Jul 2015 10:52:21 GMT

Thanks alot...swuehl.

Re: Regarding Avg

d_koti24 — Wed, 15 Jul 2015 11:14:59 GMT

Hi swuehl,

It's working fine.But I want to small enhancement to that.

if i select

field1=40,

filed2 ,and field3 shows upto 60 ,

if i select field1=40, field2 =10

filed3 shows values upto 50 like 0,5,10,15,20,25,30,35,40,45,50.

but my requirement is i don't want show upto 50 values for last field selection .instead of dispalying all values i want to display remining value.that is only 50.

for ex:

filed1:40 field1=40

filed2 i want show upto 60,if i select 30 in filed2 , filed2=30 i.e 40+30=70

in filed3 i want display only 30 value . instead of 0,5,10,15,20,25,30

regards,

Re: Regarding Avg

swuehl — Wed, 15 Jul 2015 11:29:00 GMT

I've added a condition for Field3 selector in attached sample.

You can add similar variables and condition for the other two fields.

Since there is only one possible value to select left, you can also create a action to automatically select this value.

Re: Regarding Avg

d_koti24 — Wed, 15 Jul 2015 12:53:23 GMT

Thank you..

Re: Regarding Avg

d_koti24 — Wed, 15 Jul 2015 13:19:16 GMT

Hi Swuehl,

Sorry For Disturbing ,

Suppose if i select 100 in field1

field2 and field3s are 0's.

In my App ,when i select 3 fields then that time only chart will display.

if i select field1=100,

then i need select remaining 2 fields also. like field2=0,filed3=0;

what i want is, when i select any field as 100,then remaining 2 fields automatically get selected as 0's,instead of selecting.

How to achive this?

that 3 fields i was loaded through inline load and concatenated.

Thanks &Regards,

Re: Regarding Avg

swuehl — Wed, 15 Jul 2015 17:12:14 GMT

Attached a version with some variable OnChange actions.