Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis and if statements!

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







11 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

Thanks Oleg,

Could you post the link to the wiki page? I'll try and implement it if I can.

Regards,

bc

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Here iit is:

http://community.qlik.com/wikis/qlikview-wiki/using-flags-to-avoid-if-formulas.aspx

good luck!

Oleg

Not applicable
Author

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!





Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

I'm not sure what's causing the problem in your case. It's hard to guess without looking at the whole picture.

  • If anything else looks absolutely accurate,
  • and all 4 individual expressions without IF statements work as expected,
  • and they only stop working when combined with IF statements

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:

  • In your load script, in the table that holds UnitDesc, add a flag as following:

IF(UnitDesc = 'Debt and Treasury Technology' , 1, 0) as DTT_Flag

  • In your expression, replace the IF by the Flag, like this:


if(getcurrentfield(Time)='MonthYear',
DTT_Flag * ( $(TomsSystemChoice) ) + (1-DTT_Flag) * ( $(vMonthlyUptime)),


DTT_Flag * $(TomsSystemsWeekly) + (1-DTT_Flag) * ($(vWeeklyUptime))

)



Hope it helps,

Oleg





rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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?

Not applicable
Author

Do you have an example file you can upload?

Not applicable
Author

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