topic Re: set analysis Query in QlikView

set analysis Query

Fri, 17 Feb 2012 10:27:55 GMT

Hi All,

I have an expression written in if condition would like to change it to set analysis..Here are those expressions..

If condition : sum(Flag_ID*if(Year = {'$(vMaxYear)'} Sales)

Set analysis : sum(Flag*_ID* sum({< Year= {'$(VMaxYear)}>} Sales ) )

The set analysis is not working.. can some one tell me what is going wrong in this...

Thanks in advnace....

set analysis Query

lironbaram — Fri, 17 Feb 2012 10:43:56 GMT

just a quick look you seem to miss on ' in the set analysis and the variable is written diffrently in the set analysis

and you need the set analysis in both sums

may be try

sum(({< Year= {'$(vMaxYear)'}>} Flag*_ID* sum({< Year= {'$(vMaxYear)'}>} Sales ) )

Re: set analysis Query

Fri, 17 Feb 2012 11:10:53 GMT

Hi Liron,

Let me explain u more clearly. I have a variable named vTest which holds the name of a flag field(Flag_ID).Flag_ID has 0,1 values..

here is my if condition : sum([ $(=vTest)] * if(Year = vMaxyear ,Sales))

I want the above expressiojn to be changes to set analysis completely..

The expression u have provided is not working..

Please let me know if u have any more suggestions..

Thanks

Re: set analysis Query

Fri, 17 Feb 2012 11:56:39 GMT

Hi All,

Do any one have any suggestions???

Re: set analysis Query

CELAMBARASAN — Fri, 17 Feb 2012 11:59:49 GMT

Hi,

Try using this expression

sum({< Year= {$(=VMaxYear)}>} Sales )* Flag_ID

Hope it helps

Celambarasan

Re: set analysis Query

Fri, 17 Feb 2012 12:04:09 GMT

Hi,

Thanks for reply..

But that is not working

Re: set analysis Query

CELAMBARASAN — Fri, 17 Feb 2012 12:08:27 GMT

Hi,

What dimensions you have in that chart?

Your expression Needs Aggr function I think.

May be like this

Sum({< Year= {$(=VMaxYear)}>} Aggr(Flag_ID *Sum(Sales),UniqueFieldName))

Celambarasan

Re: set analysis Query

Fri, 17 Feb 2012 12:20:08 GMT

Hi,

I have VALUELIST function used in the dimesnion to calculate the dimension names dynimically in the chart.. In the expression tab the same VALUELIST is used and to that different conditions are given to pick the appropriate calculation for the different dimension values declared in the Dimensions tab..

I am testing the expression u have given in the text object.. It is not returning any value..

Plz let me know if u need any clarification..

Thanks

Re: set analysis Query

CELAMBARASAN — Fri, 17 Feb 2012 12:26:34 GMT

Hi,

Flag_ID is a variable or field?

If Field what is the use of that? because you stated that it has 0,1 values only then if you multiply with 0 then it will get zero for some results?

If Flag_ID is field then try with this expression

=Sum({< Year= {$(=VMaxYear)},Flag_ID={1}>} Sales)

Celambarasan

Re: set analysis Query

Fri, 17 Feb 2012 12:40:33 GMT

I have a variable vTest which holds the name of the filed called Flag_ID.. the expression u have posted seems to be good but I have tested the same in text object it does not return any value...

Re: set analysis Query

CELAMBARASAN — Fri, 17 Feb 2012 12:44:05 GMT

Hi,

Check the expression in the variable for vMaxYear.

=Sum({< Year= {$(vMaxYear)},Flag_ID={1}>} Sales)

if its perfect this should work.

Celambarasan

Re: set analysis Query

Fri, 17 Feb 2012 12:55:38 GMT

Fine thanks for the reply..

Can u plz tell me whether the below two expressions would give the same result ot not..

1.sum(total <[Class]> if year= vmaxyear,Sales)

2.sum({< Class=,Year = {'$(VMaxYear)'} >} Sales)

The Vmaxyear is a variable which calculates the Max year i.e Max(Total Date)

Thanks

Re: set analysis Query

CELAMBARASAN — Fri, 17 Feb 2012 13:10:02 GMT

Hi,

2nd one will work but you have not include Flag_ID there?

Then VMaxYear Variable expression should Max(Year) only know?but you used Date field there.

Celambarasan

Re: set analysis Query

Fri, 17 Feb 2012 13:30:39 GMT

Hi,

plz forget the expression that I have posted previously.. Both are different.. I just wanted to check with you teh way I have implemented the if COndition and the set analysis now is correct or not...

In IF condition I have given total (Class) , but in set analysis have mentioned the same in Class = , Would both of this expressions will give the same result???

Re: set analysis Query

CELAMBARASAN — Fri, 17 Feb 2012 13:35:09 GMT

Hi,

No because 2nd one excludes the selection in the field class but 1st one includes the selection.

Celambarasan

To make correction:sum({<Year = {'$(VMaxYear)'} >} Total <Class> Sales) this and the first one are same.

Re: set analysis Query

Fri, 17 Feb 2012 13:38:18 GMT

Hi can u plz tell me how do I include the selection in set analysis.. Could u edit the expression written in set analysis and let me know it..

Thanks

Re: set analysis Query

Fri, 17 Feb 2012 13:47:32 GMT

Thanka alot for ur time..

set analysis Query

CELAMBARASAN — Fri, 17 Feb 2012 13:49:50 GMT

Hi,

But still not arrived the solution of your problem.

Celambarasan