topic Set Analysis woes in QlikView

Set Analysis woes

jduenyas — Mon, 10 Nov 2014 08:25:42 GMT

Hi all

It seems that there are many questions regarding set analysis which just points to the complexity of the subject and I am no exception.

I have created a variable vTrial as Makedate(Year(Today())-1,month(today()), Day(Today())) to get today's date as of last year.

When I use the variable in a text boox as $(vTrial) last year's date is returned.

When using this variable to get sum of values, the sum of the whole data set is returned. the formula/expression is like so:

=sum({<TranDate={"=$(vTrial)"}>}Expression4)

When I use the same variable with an IF(...) Statement the correct results are returned

=sum(if(TranDate=$(vTrial),Expression4))

Further, when using the expanded formula that is used to create the variable instead of the variable, whole data set is returned as the sum.

=sum({<TranDate={"=$(=MakeDate(Year(today())-1,Month(Today()),Day(Today())))"}>}Expression4)

To say the least it is quite frustrating.

Is there a concise source that explains the ins and outs of the nomenclature and correct punctuation to get the expressions to yield correct results? I am using set analysis but resort to use only with "light weight" expressions such as Year={2014} etc. (Not a good way to do set analysis!!!)

I came across some blogs and articles but none were able to shed light in a concise way.

The sample application is attached

Thanks in advance

Josh

PS you may see from back track that I am not new to QV but still am feeling the pain...

Re: Set Analysis woes

Gysbert_Wassenaar — Mon, 10 Nov 2014 10:28:05 GMT

Makedate(Year(Today())-1,month(today()), Day(Today()))

Put an = sign in front of the expression in the variable. You can also use =addyears(today(),-1).

=sum({<TranDate={"=$(=MakeDate(Year(today())-1,Month(Today()),Day(Today())))"}>}Expression4)

If you remove the = sign in front the the dollar expanded expression it will work.

sum({<TranDate={"11/10/2013"}>} Expression4) works, but sum({<TranDate={"=11/10/2013"}>} Expression4) does not.

Thus: =sum({<TranDate={"$(=MakeDate(Year(today())-1,Month(Today()),Day(Today())))"}>}Expression4)

Re: Set Analysis woes

jduenyas — Mon, 10 Nov 2014 15:09:15 GMT

Thank you Gysbert. I was hoping to receive a reply from you and you came through. You certainly know the ways in QV.

However, this put an emphasis on my request for a source that will explain the correct punctuation of set analysis.

Is there such a written source somewhere?

Again thank you.

Josh