topic Re: between dates in variable set analysis in QlikView

between dates in variable set analysis

Anonymous — Wed, 02 Sep 2015 16:39:37 GMT

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

Re: between dates in variable set analysis

lironbaram — Wed, 02 Sep 2015 16:49:55 GMT

why not use

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

Re: between dates in variable set analysis

maxgro — Wed, 02 Sep 2015 17:23:31 GMT

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

Re: between dates in variable set analysis

Anonymous — Wed, 02 Sep 2015 18:31:26 GMT

hi,

Try

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

Re: between dates in variable set analysis

sasiparupudi1 — Wed, 02 Sep 2015 19:01:27 GMT

Use it in double quotes

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

hth

sasi

Re: between dates in variable set analysis

engishfaque — Thu, 03 Sep 2015 03:36:45 GMT

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

Re: between dates in variable set analysis

jagan — Thu, 03 Sep 2015 04:14:40 GMT

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.

Re: between dates in variable set analysis

Anonymous — Thu, 03 Sep 2015 07:41:27 GMT

Hi,

Thanks a lot it works

Re: between dates in variable set analysis

Anonymous — Thu, 03 Sep 2015 07:41:47 GMT

perfect thanks

Re: between dates in variable set analysis

Anonymous — Thu, 03 Sep 2015 07:43:38 GMT

Dear Ahmed,

Thank you for tour response it"s a good solution

best regards

Re: between dates in variable set analysis

Shubham_Deshmukh — Tue, 30 Jul 2019 14:03:43 GMT

What if Date is coming from formula or variable ?