Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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.
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?
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!
Check this!
Fei !
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