Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
Apologies, but this is a difficult one to try and explain, and I am unable to send the application.
I have 2 variables: vmonthlyuptime and system choice.
each variable is a very long equation with set analysis implemented so that if a date is selected it will not affect the charts on the dashboard:
The set analysis is correct. However when I try and combine the variables into an if statement the set analysis does not appear to work:
My equation is:
if(getcurrentfield(Time)='MonthYear', (if(UnitDesc='Debt and Treasury Technology', $(TomsSystemChoice), $(vMonthlyUptime))), (if(UnitDesc='Debt and Treasury Technology', $(TomsSystemsWeekly),$(vWeeklyUptime))))
It says the expression is correct, but when a date is selected the figures change which is not what I want.
Could anyone think of something that may be wrong with this? I'm really at a loss as to see what is wrong.
Thanks in advance,
BC
I can't see anything that would be obviously wrong with this expression, so the only thing I can offer is some "debugging" directorions:
1. What if you try and add 4 independent expressions with the 4 variables with no IF statements and see if they behave like you expected.
2. With complex expressions, like this one, the Expression syntax checker is usually getting "confused" and it stops identifying errors - any expression appears as "OK".
3. Try to find a way to simplify your expression - after all, you have 2 nested IF statements, with 4 complex expressions with Set Analysis... I wouldn't be surprised if QlikView is getting "confused" with such a complex structure... You can easily avoid at least one "IF" - read my Wiki about using flags. This is perhaps another case of using Flags:
Instead of
if(UnitDesc='Debt and Treasury Technology', <expr1>, <expr2>),
you can calculate a flag - let's call it DTT_Flag that will have 1 when your condition is true and 0 if it's false. Then, your expression could look like:
<Expr1>* DTT_Flag + <Expr2>*(1-DTT_Flag)
Good luck!
Oleg
Thanks Oleg,
Could you post the link to the wiki page? I'll try and implement it if I can.
Regards,
bc
Here iit is:
http://community.qlik.com/wikis/qlikview-wiki/using-flags-to-avoid-if-formulas.aspx
good luck!
Oleg
Hi Oleg,
I understand what way the flags work but I'm not sure what way I should go about trying to create one as I have numerous nested fields?
if(getcurrentfield(Time)='MonthYear', (if(UnitDesc='Debt and Treasury Technology', $(TomsSystemChoice), $(vMonthlyUptime))),
(if(UnitDesc='Debt and Treasury Technology', $(TomsSystemsWeekly),$(vWeeklyUptime))))*/
The more I look at the expression, the more confused I get!
I'm not sure what's causing the problem in your case. It's hard to guess without looking at the whole picture.
then the logical conclusion is that QlikView didn't like the combination of nested IFs with the Set Analysis expressions inside. If that's the conclusion (again, I'm not sure it is because I can't see your app), - then you can at least avoid one IF by replacing it with the flag:
IF(UnitDesc = 'Debt and Treasury Technology' , 1, 0) as DTT_Flag
if(getcurrentfield(Time)='MonthYear',
DTT_Flag * ( $(TomsSystemChoice) ) + (1-DTT_Flag) * ( $(vMonthlyUptime)),
DTT_Flag * $(TomsSystemsWeekly) + (1-DTT_Flag) * ($(vWeeklyUptime))
)
Hope it helps,
Oleg
Could the problem be due to when the formula in the variable is getting calculated? If you paste the variabl formula directly into the expression, does it work correctly?
-Rob
Hi Rob,
It's strange because I broke the problem down into parts and it seemed to work.
The graph I had, had an option on the x-axis to switch between a monthly view or a view over the past twelve weeks. I can create two separate graphs where one has the monthly view and one has the weekly view. Each works and is unaffected by date selection(set analysis working). However, when I use the if condition so that the two graphs work as one, the set analysis doesn't work.
I'm at a loss as to why this is happening....Any other thoughts?
Do you have an example file you can upload?
Thanks Oleg,
This does indeed work but the set analysis does not seem to work, ie when a date is selected only one column shows..I would like this to show the same figures regardless of date selection (as it is a trending graph over time).
If I could get this working it would be perfect!
Any more thoughts would be much appreciated 🙂
bc