topic Set analysis with MakeDate in QlikView

Set analysis with MakeDate

Tue, 13 Jul 2010 15:48:18 GMT

Another set analysis problem. [:P]

To validate I've added

if(MakeDate(Year, num(Month),Day)<= Date(Today()),1,0)

in the table and it evaluates as expected.

Unfortunately this set analysis expression doesn't:

=Sum({$<MakeDate(Year,num(Month),Day)={'<=$(=Date(Today()))'}>} Budget_ValueSEDay)

I suppose there is some syntax problems, right?

Tue, 13 Jul 2010 16:27:30 GMT

You Set expression is not correct. Instead of "MakeDate(Year,num(Month),Day)" you should use a Field name which you want use for a selection.

Check the syntax:

set_modifier ::= < field_selection {, field_selection } >

field_selection ::= field_name [ = | += | ¬-= | *= | /= ] element_set_expression

element_set_expression ::= element_set { set_operator element_set }

element_set ::= [ field_name ] | { element_list } | element_function

element_list ::= element { , element }

element_function ::= ( P | E ) ( [ set_expression ] [ field_name ] )

element ::= field_value | " search_mask "

Tue, 13 Jul 2010 16:32:53 GMT

You can do something like this:


left join (YourTable)
Load *,
if(MakeDate(Year, num(Month),Day)<= Date(Today()),1,0) as YourFlag
resident YourTable;

After this you can use "YourFlag" field in your expression:


=Sum({$<YourFlag={"<=$(=Date(Today()))"}>} Budget_ValueSEDay)

Wed, 14 Jul 2010 10:34:29 GMT

Thank you for your responses.

The syntax was of course not correct - but it would have been great if my expression woule have evaluated.

Now I create the Date in a load script.