topic Re: Easy Sum with Variable Question in QlikView

Easy Sum with Variable Question

Thu, 30 Aug 2012 22:25:14 GMT

As part of this trying to convert an end user entered Date (on a calendar element) inside the expression, in a normal "If" statement it works fine as Date(vDateEntered) to converts successfully from number to date format.

Sample: =Sum(If(SalesDate=Date(vDateEntered), Sales)) works perfectly.

But converting this example to set analysis isn't converting to date mm/dd/yyyy format correctly.

=sum({<SalesDate={'=Date$(vDateEntered)}>} Sales)

(just summing up sales for whatever date a person picked)

Can you tell me the right syntax for this? Thanks much!

Re: Easy Sum with Variable Question

Fri, 31 Aug 2012 04:02:36 GMT

=sum({<SalesDate={"$(=Date(vDateEntered))"}>} Sales)

Kiran Rokkam.

Re: Easy Sum with Variable Question

Fri, 31 Aug 2012 14:51:09 GMT

Thats perfect, one new issue, SalesDate is stored in the format 1/31/2011 12:00:00 AM, so i need to use Date(SalesDate) to convert it to 1/31/2011.

How can i get that SalesDate conversion into your syntax?

Thanks again very much!

Re: Easy Sum with Variable Question

Fri, 31 Aug 2012 16:44:50 GMT

Set analysis can be applied only on the field and hence its essencial to apply the format of the conditional expression to be same as the the field format. You can modify the expression to:

sum({<SalesDate={"=TimeStamp$(vDateEntered)"}>} Sales)

if it not works try

sum({<SalesDate={"=Text(TimeStamp$(vDateEntered))"}>} Sales)

Idea is to match expression return type to the field format. Try around with the expression and nplay around with the formats to match the return to the field. You can check the return by tryin the set expression in a text box.

Regards,

Kiran Rokkam.

Re: Easy Sum with Variable Question

Fri, 31 Aug 2012 21:02:56 GMT

Thank you very much!