Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

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

1 Solution

Accepted Solutions
xufei123
Valued Contributor

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

Check this!

Fei !

8 Replies
chooco_co
Valued Contributor

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

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

rsw
Contributor II

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

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

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

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.

Not applicable

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

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.

Not applicable

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

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
New Contributor II

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

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!

xufei123
Valued Contributor

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

Check this!

Fei !

Not applicable

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

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

Community Browser