between dates in variable set analysis

[Anonymous]

Hi

I have a calculation giving me a date range ' > =' & date (min (Date) ) & '< =' & date ( AddYears ( max ( date) , - 1 ) )

But when I define this calculation as a variable that does not work in the set analysis

= sum ( { <Date = { $ (variable) } >} [Ca CDE ] )

Can you help me?



Thank you

[lironbaram · Accepted solution]

why not use

sum ( { <Date = {"> =$(= date (min (Date) ) )  < =$(=date ( AddYears ( max ( date) , - 1 ) ))"} >} [Ca CDE ] )

[maxgro]

this is my expression

sum({$ <d={"$(variable)"} >}  [Ca CDE ] )

and this variable (with equal sign), the date field is d I used to test it

= '>=' & date(min(d)) & '<=' & date ( AddYears ( max (d), -0 ) )

and it works

you have the field date and Date (Qlik is case sensitive!!!)

EDIT

attached an example

[Anonymous]

hi,

Try

=Sum({<Date = {">=$(=Date(Min(Date))) <=$(=Date(Max(Date)-1))"}>}[Ca CDE ])

[sasiparupudi1]

Use it in double quotes

sum ( { <Date = { "$ (variable) "} >} [Ca CDE ] )

hth

sasi

[engishfaque]

Dear Saoud,

Please first check your variable value either it's coming in number format or date format. If it is coming in number format then please use Data function and convert into proper format.

However, please use single quotation and double quotation in against of Dollar sign expansion to check which one is working.

Kind regards,

Ishfaque Ahmed

[jagan]

Hi,

Instead of storing expression in single variable, create two variables with start and End dates like below and use those in Set analysis expression, this will make the expression meaningful to read and easily to understand and debug if there is any issue.

vStartDate = date (min (Date) )

vEndDate = date ( AddYears ( max ( date) , - 1 ) )

Variables in expressions:

= sum ( { <Date = { '>=$(=vStartDate)<=$(=vEndDate ) ' } >} [Ca CDE ] )

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Hi,

Thanks a lot it works

[Anonymous]

perfect thanks

[Anonymous]

Dear Ahmed,

Thank you for tour response it"s a good solution

best regards