I am stuck and can't get a handle to solve this issue

[#1]

Using Qlik Sense 3.0 I am having a terrible time figuring this out, am I just missing the forest amongst the trees? Any help would be appreciated

Result: I am looking for EffectiveDate (Fieldname) that fall between two variables vFromDate and vToDate. with an ExpriationDate (Fieldname) >= FromDate

Variable Values:   vFromDate = 5/1/2016 and vToDate = 9/16/2016

Formula:

EffectiveDate<= vToDate AND ExpirationDate >= vFromDate

Need this as an IF(EffectiveDate<= vToDate AND ExpirationDate >= vFromDate ,EffectiveDate) that I can use in a table

Regards

-bill

[marcohadiyanto]

Hi Bill,

Where are you planing to create EffectiveDate? is it in data load editor or edit mode (visual)?

i assume you create in edit mode.

When you want to call variable you should add $() so for vToDate change to $(vToDate).

Regards,

Marco

[Anonymous]

Hi Bill,

I think Marco has likely provided you the answer, you need to use dollar sign expansion with your variable. There's a brilliant article on how it works here: The Magic of Dollar Expansions

Cheers,

Rod

[Anonymous]

Do check what format your date variables and fields are in.

If your variable is in text format and your field is in numeric format it will not work.

[#5]

Hi Bill,

My suggestions are ,

1. try to use the same formula in expression rather than dimension level.

2. Use set analysis Only({<EffectiveDate = {">vFromDate <=vToDate"}>} EffectiveDate)

3. check the date format of variable and field then maintain in a unique format.

Regards,

Ravi Kancharla.

[#6]

Thank you all for you responses. I have tried using the $ Expansion but still cannot filter the table. In my original post I stated I am trying to return only the rows that fall between the 2 variables. I had tried this expression behind the EffectiveDate ( which is a data field ) :

Hoping to return all rows between the variales vFromDate thru vToDate)

IF(Num(EffectiveDate) <= num(vToDate)  AND ExpirationDate >= num(vFromDate) ,EffectiveDate))

No results!

Tried:

IF(Num(EffectiveDate) <= num($vToDate)  AND ExpirationDate >= num($vFromDate) ,EffectiveDate))

No luck.....

What can I possibly be doing wrong? Am I over thinking this? ugh?

[javierortiz79]

Hello Bill,

You have 2 options:

1.- Use Set analysis, that way in your chart you will only get the dates that set analysis is evaluating.

2.- Create a field with the if statement IF(EffectiveDate<= vToDate AND ExpirationDate >= vFromDate ,EffectiveDate)

     as you mentioned in your post.

Either the option 1 or the 2 will solve your issue.

Regards!

[NZFei · Accepted solution]

Check this!

Fei !

[#9]

Fei

Awesome, that's exactly what I needed, it appears I tried every thing except placing single quotes around the expanded variables.

Thanks all who replied, this community is the very best.

Thanks again !

Bill