topic Re: set analysis value in QlikView

set analysis value

felcar2013 — Mon, 20 Jul 2015 10:40:44 GMT

i have two variables:

let vTest2 = max(month_ID)

let vTest = fieldname (numeric,1,2,3,4 ...12)

in TextBox, this works: $(vTest2)-$(vTest)

but in set Analysis no

=num(Count({$<month_ID ={"=$(=month_ID)- $(vTest)"}>}distinct customer_id),'#.##0')

i want to deduct both variables, how should i do this?

thanks

felipe

Re: set analysis value

Anonymous — Mon, 20 Jul 2015 10:44:44 GMT

is there only one month_id selected?

in vTest2 you define max(month_id)

try to select ONE month. >Does the set Analysis still is wrong?

Re: set analysis value

avinashelite — Mon, 20 Jul 2015 10:56:03 GMT

we are not able to replicate the issue can you please share the sample data or app

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 11:24:26 GMT

i select always one month and the set is wrong

Re: set analysis value

sushil353 — Mon, 20 Jul 2015 11:30:30 GMT

hi,

try if this works

=num(Count({$<month_ID ={"=$(=Max(month_ID)- $(vTest))"}>}distinct customer_id),'#.##0')

=num(Count({$<month_ID ={'$(=Max(month_ID)- $(vTest))'}>}distinct customer_id),'#.##0')

HTH

Sushil

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 11:33:10 GMT

hi thanks,

i tried that already. QV reads only the first Expression: =Max(month_ID), but not the $(vTest)

Re: set analysis value

Anonymous — Mon, 20 Jul 2015 11:41:44 GMT

you tried to provide a third variable

vNew = vTest2-vTest

and use

the new variable

=num(Count({$<month_ID ={"=$(=(vNew)"}>}distinct customer_id),'#.##0')

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 11:42:21 GMT

data structure is like this ( i cannot use the data)

month_Id,customerID

1,1

1,2

1,3

1,4

1,5

2,1

2,2

2,3

2,4

2,5

2,6

3,1

3,2

3,3

3,4

3,5

3,6

3,7

vTest is a field, to be selected in filter

load * inline [field

12]

Re: set analysis value

sushil353 — Mon, 20 Jul 2015 11:46:35 GMT

Hi,

Try this one: =num(Count({$<month_ID ={'$(=Max({1}month_ID)-$( vTest))}>}distinct customer_id),'#.##0')

Re: set analysis value

jfkinspari — Mon, 20 Jul 2015 11:47:34 GMT

What's up with the

let vTest = fieldname (numeric,1,2,3,4 ...12)

The fieldname function requires a field number and a table name.

You could start by replacing it with an integer, and see if that gives you a result of the set expression

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 11:55:26 GMT

Not working. vNew works, but not in the set Analysis expression

Re: set analysis value

sushil353 — Mon, 20 Jul 2015 11:58:22 GMT

if vnew is working then this should work: =num(Count({$<month_ID ={'$(vNew)'}>}distinct customer_id),'#.##0')

if this wont work then share ur sample app.

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 12:06:16 GMT

this works, when using the field month_ID directly:

num(Count({$<month_ID ={$(=max(month_ID)-$(vTest))}>} distinct customer_id),'#.##0')

when using a Trigger in another field "month_text", toggle select month_ID, it does not work any more.

Re: set analysis value

avinashelite — Mon, 20 Jul 2015 12:18:39 GMT

This statement wont work in the script :

let vTest2 = max(month_ID)

if you want the max then you need to do a group by on the key field otherwise it wont fetch the max value.

can you please share the your app??

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 12:29:15 GMT

it fetches the max value. as said, this formula worked num(Count({$<month_ID ={$(=max(month_ID)-$(vTest))}>} distinct customer_id),'#.##0')

the issue is also, that i am using two fields as filter

month_text (names of months and year)

month_ID used in formula

in the UI the user shall select a month from the filter month_text and the value selected shall activate the month_ID field (in the Background). The month_ID value is used in the Expression. I used for this toggle select on the field month_text and activated the month_ID, which works correctly, however, the selected month_ID is not activating the Expression. To activate the Expression i Need to select directly the month_ID, what is what i do not want.

i cannot share my app (more than 700 millions, but the data I submitted previously reflects the data structure.

Re: set analysis value

jfkinspari — Mon, 20 Jul 2015 12:34:30 GMT

What is the relationship between the fields month_text and month_ID?

When you selects a value in month_text and the actions does it's thing on Month_ID, how do the Month_ID listbox then look?

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 12:36:49 GMT

hi, it Looks like this

month_id, month_text

90,Jul.2015

89,Jun.2015

88,May.2015

.......

bis Jan.2011

Re: set analysis value

jfkinspari — Mon, 20 Jul 2015 12:41:23 GMT

All right,

It's unclear why a selection in month_id performs differently than a selection in month_text, as the relationsships seems to be 1:1 based on Graphics above.

When you select a single value in month_text, is more that one month_id then possible?

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 12:42:00 GMT

in the User Interface i use the filter "month_text" and the user selects, for example Jul.2015, then in the Expression i use the "month_ID" field.

this means, when the user selects Jul.2015, the field "90" is selected. For this i used toggle select as Trigger on the field "month_text". The value "90" is selected (green), but the Expression does not work.

Re: set analysis value

felcar2013 — Mon, 20 Jul 2015 12:42:39 GMT

no, this is not possible