topic Re: Filter Chart with variable in QlikView

Filter Chart with variable

danosoft — Thu, 28 Jun 2018 12:41:47 GMT

Hi i have those two variables:

Set vAnnoTempo = GetFieldSelections(ANNOTEMPO);

Set vMeseTempo = GetFieldSelections(MESETEMPO);

ANNOTEMPO and MESETEMPO are fields of my TIME_TABLE

i want those variables takes from my fields selection:

When i choose for example Anno: 2017 Mese: 2

I want my chart will be filter from my vAnnoTempo and vMeseTempo, but its not start

The set analisys expression i have in my chart is:

=Sum({<ANNO_IN={"=$(vAnnoTempo)"},MESE_IN={"=$(vMeseTempo)"}>} CONSUMO_TOT_ANNUO)

ANNO_IN and MESE_IN are my fields in my CONTR table.

p.s.: CONTR table and TIME_TABLE are not connected, and this is correct, i not want are connected, i want do my filter with set analisys in this way, becouse for every dimension i have in my chart i need to do a different set analisys

Thanks

Re: Filter Chart with variable

olivierrobin — Thu, 28 Jun 2018 14:20:38 GMT

hello

where do you set your variables ? why do you need to use them ?

why don't you crate 2 listboxes, 1 for ANNO, 1 for MESE so you graph will take those values directly

Re: Filter Chart with variable

danosoft — Thu, 28 Jun 2018 14:47:45 GMT

Hi i set the variable in the SCRIPT (where i write LOAD and others)

i have create 2 listboxes with those sure, but the problem is my graph is not CONNECTED to that table, i use those only for do my set analisys, my graph must be populate with some columns with some different set analisys.

Like i wrote my set analisys for my first expression must be:

=Sum({<ANNOIN={"=$(vAnnoTempo)"},MESEIN={"=$(vMeseTempo)"}>} CONSUMO_TOT_ANNUO)

for other expressions i need to do others set analisys...

and my CONTR table (the table where i do the graph) is NOT connected with my TIME_TABLE (where i set my variables) so i need to do this set analisys....

btw i not understand why this set analisys not works, becouse is if i display in a text area my $(vAnnoTempo) and my $(vMeseTempo) it show correctly, and if i change from my list box the ANNOTEMPO and MESETEMPO the variables changes correctly... but my set analisys not works

Re: Filter Chart with variable

olivierrobin — Thu, 28 Jun 2018 14:57:26 GMT

when you say your set analysis doesn't work, does it mean no result or does the graph display all asif there was no selection ?

if it is the 2nd, that means the values are empty, so the set analysis selects all

try to write it this way :

=Sum({<ANNOIN={'$(vAnnoTempo)'},MESEIN={'$(vMeseTempo)'}>} CONSUMO_TOT_ANNUO)

Re: Filter Chart with variable

danosoft — Thu, 28 Jun 2018 15:04:21 GMT

the 2nd, it display the all, but the values of my variables are not empty becouse if i display in a text box it show me the values

your set analisys not works, it show me nothing, 0

thanks

Re: Filter Chart with variable

olivierrobin — Thu, 28 Jun 2018 15:08:15 GMT

so that means it filters something (even if is nothing)

when you say : i set the variable in the SCRIPT (where i write LOAD and others)

it means the values are never refreshed

how do you refresh them with the selections made ?

Re: Filter Chart with variable

danosoft — Thu, 28 Jun 2018 15:12:49 GMT

i set it in the script yes in this way:

Set vAnnoTempo = GetFieldSelections(ANNOTEMPO);

Set vMeseTempo = GetFieldSelections(MESETEMPO);

in my visualization i have two select list, like you see in this image:

when i choose the 2017 for example in my text box showing the $(vAnnoTempo) i see: 2017

if i choose the 2018 in my text box showint the $(vAnnoTempo) i see: 2018

but in my set analisys in the expression in my graph is like it nothing do...

Re: Filter Chart with variable

olivierrobin — Thu, 28 Jun 2018 15:32:01 GMT

can you post the part of your application containing the grah, the boxes, the script and a piece of data ?

Re: Filter Chart with variable

danosoft — Thu, 28 Jun 2018 15:39:31 GMT

post the application is impossible but i will try to do an example... sincerly is strange i thought the set analisys is correct....

Re: Filter Chart with variable

danosoft — Thu, 28 Jun 2018 15:48:21 GMT

This is... you can look vAnnoTempo text box display the right value i choose 2018, but the set analisys show me a big big value... it not filter : =Sum({<ANNOIN={"=$(vAnnoTempo)"},MESEIN={"=$(vMeseTempo)"}>} CONSUMO_TOT_ANNUO)

Re: Filter Chart with variable

olivierrobin — Thu, 28 Jun 2018 15:51:07 GMT

ok, i created a sample appli on my side

and it seems to work

but I see a problem in the definition of the variables you made in the post.

Verify the code below

TIME_TABLE:

load * inline [

ANNOTEMPO,MESETEMPO

2017,12

2018,1

2018,2

]

;

CONTR:

load * inline [

ANNO_IN,MESE_IN,CONSUMO_TOT_ANNUO

2017,12,201712

2018,1,201801

2018,2,201802

];

Set vAnnoTempo = GetFieldSelections(ANNO_IN);

Set vMeseTempo = GetFieldSelections(MESE_IN);

Re: Filter Chart with variable

olivierrobin — Thu, 28 Jun 2018 15:54:50 GMT

oups !!!!!!

sorry for the mistake

it's really

Set vAnnoTempo = GetFieldSelections(ANNOTEMPO);

Set vMeseTempo = GetFieldSelections(MESETEMPO);

and the result is :

Re: Filter Chart with variable

olivierrobin — Thu, 28 Jun 2018 16:01:09 GMT

very strange, it is as if the selection was inactive and it always shows the last month

Re: Filter Chart with variable

olivierrobin — Fri, 29 Jun 2018 07:23:32 GMT

hello

well, i don't know why it doesn't work with variables but the syntax below works

=Sum({<ANNO_IN={'$(=GetFieldSelections(ANNOTEMPO))'},MESE_IN={'$(=GetFieldSelections(MESETEMPO))'}>} CONSUMO_TOT_ANNUO)

Re: Filter Chart with variable

danosoft — Fri, 29 Jun 2018 09:18:27 GMT

Thanks, yes is really strange with variable not works..... i used your expression, but if a choose more months (1,2,3) it show me 0

Re: Filter Chart with variable

olivierrobin — Fri, 29 Jun 2018 09:23:30 GMT

if you want to select more then 1 value in a set analysis, you should code it this way

var={'v1','v2', ..... ,'vn'}

sto try something like

concat(getfield......,',') and maybe a quote somewhere

Re: Filter Chart with variable

danosoft — Fri, 29 Jun 2018 09:26:30 GMT

if i use:

=Sum({<ANNOIN={'$(=GetFieldSelections(ANNOTEMPO))'},MESEIN={'$(=CONCAT(GetFieldSelections(MESETEMPO)","))'},STATO_IN={'INGRESSO'}>} CONSUMO_TOT_ANNUO)

it not work

Re: Filter Chart with variable

olivierrobin — Fri, 29 Jun 2018 09:46:58 GMT

try with this concat

Concat(GetFieldSelections(Field name, chr(39) & ',' & chr(39)))

replace field name with the one desired

Re: Filter Chart with variable

danosoft — Fri, 29 Jun 2018 09:56:19 GMT

Yes, you are Super Oliver, it work..... but now i have another question:

i have to make an Aggregation for take only 1 row on the table for my field: COD_PUNTO, takinga the Max of my DATA_IN, but if i use in my set analisys this, it not work correctly:

DATA_IN={"=Aggr(Max(DATA_IN), COD_PUNTO)"}

so my all set analisys become like this:

=Sum({<DATA_IN={"=Aggr(Max(DATA_IN), COD_PUNTO)"},ANNOIN={'$(=GetFieldSelections(ANNOTEMPO))'},MESEIN={'$(=Concat(GetFieldSelections(MESETEMPO, chr(39) & ',' & chr(39))))'},STATO_IN={'INGRESSO'}>} CONSUMO_TOT_ANNUO)

where is the problem?

p.s.: i ask if you can post your .qvw you did for your example post before

Re: Filter Chart with variable

olivierrobin — Fri, 29 Jun 2018 10:01:13 GMT

enclose you aggr in $(=) as for the other functions