topic Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value]) in QlikView

Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

rahulhv1 — Wed, 20 Aug 2014 04:25:03 GMT

Hi Guys,

above mentioned set analysis statement is not working. in above example. VselectDate is an input box variable where in user can enter the date as per his/her wish. Please also not that date structure is same i.e. DD-MM-YYYY in variable as well as FinanceRelease field.

If i write

=Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'=$(VselectDate)'}>} [Capex Item Gross Value])

it works perfectly fine. but when i enter <= it is not working. Could any one pls. help me

-Rahul

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

tresB — Wed, 20 Aug 2014 04:37:01 GMT

Could you post your sample qvw?

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

PrashantSangle — Wed, 20 Aug 2014 04:56:41 GMT

Hi,

Use " double quote instead of ' Single quote and use = to evaluate your variable inside set analysis.

Try

Like this,

=Sum({<[Fiscal Year]= {"$(=vcurrentyear)"}, FinanceRelease = {"<=$(=VselectDate)"}>} [Capex Item Gross Value])

Regards,

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

ashfaq_haseeb — Wed, 20 Aug 2014 05:04:32 GMT

Hi,

try below

=Sum({<[Fiscal Year]= {'$=(vcurrentyear)'}, FinanceRelease = {'=$(VselectDate)'}>} [Capex Item Gross Value])

Or post you sample file.

Regards

ASHFAQ

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

Wed, 20 Aug 2014 05:10:14 GMT

Hi Rahul,

For Numeric Search strings or Text search strings in Set Analysis expressions please use double quotes. Recommed you to go through below link on usage of quotes in QlikView scripting

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/09/quoteology

Also recommend to go through "Set Analysis" chapter in "QlikView 11 for Developers" by Barry Harmsen & Miguel Garcia

So your expression would be as below

=Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {"<=$(VselectDate)"}>} [Capex Item Gross Value])

Thanks,

Sri

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

rahulhv1 — Wed, 20 Aug 2014 05:12:01 GMT

Its not working. i have tried that too. Pls. find Attached QVW AND QVD FILES.

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

rahulhv1 — Wed, 20 Aug 2014 05:12:10 GMT

Its not working. i have tried that too. Pls. find Attached QVW AND QVD FILES.

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

rahulhv1 — Wed, 20 Aug 2014 05:12:28 GMT

Its not working. i have tried that too. Pls. find Attached QVW AND QVD FILES.

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

jagan — Wed, 20 Aug 2014 05:16:50 GMT

Hi Rahul,

Try like this

=Sum({<[Fiscal Year]= {'$(=vcurrentyear)'}, FinanceRelease = {'<=$(=VselectDate)'}>} [Capex Item Gross Value])

Regards,

Jagan.

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

ashfaq_haseeb — Wed, 20 Aug 2014 05:19:43 GMT

No attachment rahul

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

jonathandienst — Wed, 20 Aug 2014 05:33:13 GMT

Its hard to diagnose the problem without a sample, but I would debug the expression like this:

Enter 2 expressions with one clause of the set expression in each and test these. This will allow you to determine which of the two clauses is failing.
Enter a literal value into the problem expression (rather than the variable). This will allow you to get the expression working without worrying about whether the variable works
Create 2 text boxes, and enter =vcurrentyear and =VselectDate into each respectively, and check that the variables are returning what you expect.
Then, and only then, put it all together in the full expression and test this again.

Remember that variable names are case sensitive, and just because the calendar box is displaying the date correctly does not necessarily mean that the variable is correctly formatted, so you might need a Date() format command in the second clause.

HTH

Jonathan

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

rahulhv1 — Wed, 20 Aug 2014 05:35:36 GMT

how to attach qvw / qvd files?

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

rahulhv1 — Wed, 20 Aug 2014 05:35:45 GMT

how to attach qvw / qvd files?

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

ashfaq_haseeb — Wed, 20 Aug 2014 05:37:16 GMT

When you click on reply.

Use advance editor, Then you will have option to attach.

Regards

ASHFAQ

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

tresB — Wed, 20 Aug 2014 05:46:09 GMT

Hi Jonathan,

Possibly this is only possible in qlik community, people who are answering are taking more pain/giving more effort than the ones who are being helped.

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

ashfaq_haseeb — Wed, 20 Aug 2014 05:47:25 GMT

True I agree with tresesco

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

rahulhv1 — Wed, 20 Aug 2014 05:48:11 GMT

am also in pain Tresesco... i have attached qvd/qvw files for reference ... pls. check.

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

tresB — Wed, 20 Aug 2014 05:56:01 GMT

Dear Rahul, Please do not read anything that hurt you from my earlier post. It was simply a light hearted expression and nothing personal.

Coming back to your question: As per your sample: here

Your FinaceRelease is a text, hence the issue. Convert this into proper date in the script using date#() like:

Load

Date#(FinaceRelease, 'DD-MM-YYYY') as FinaceRelease

Then it should work.

Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

mjayachandran — Wed, 20 Aug 2014 06:46:02 GMT

You can try:

=Sum({$<[[Fiscal Year]]={'$(vcurrentyear)'},FinanceRelease={"<=$(VselectDate)"}>}[[Capex Item Gross Value]])

=Sum({<[[Fiscal Year]]={'$(vcurrentyear)'},FinanceRelease={"<=$(VselectDate)"}>}[[Capex Item Gross Value]])

you may use the below tools also:

Set Analysis Wizard for QlikView | qlikblog.at

Re: Re: Set Analysis Issue e.g. =Sum({<[Fiscal Year]= {$(vcurrentyear)}, FinanceRelease = {'<=$(VselectDate)'}>} [Capex Item Gross Value])

ashfaq_haseeb — Wed, 20 Aug 2014 08:12:52 GMT

Hi,

Have a look at attached file.

Regards

ASHFAQ