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

[rahulhv1]

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

[jonathandienst]

Hi

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

[rahulhv1]

how to attach qvw / qvd files?

[rahulhv1]

how to attach qvw / qvd files?

[ashfaq_haseeb]

When you click on reply.

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

Regards

ASHFAQ

[tresB]

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.

[ashfaq_haseeb]

True I agree with  tresesco

[rahulhv1]

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

[tresB]

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.

[mjayachandran]

You can try:

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

or

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

you may use the below tools also:

Set Analysis Wizard for QlikView | qlikblog.at

[ashfaq_haseeb]

Hi,

Have a look at attached file.

Regards

ASHFAQ