Set Analysis Syntax

[raynac]

Hi everyone... I am hoping someone can assist.  We have had a view here for a literal decade or more that contains many set analyses, and while I am 99.9% positive that they were fine before, suddenly we are getting an error message from them.  They still appear to work fine and produce a correct number; that's the strange part.  But I need to use the numbers in other calculations and the same set analysis code seems to not be working in the new formulae.

Here is the formula.  The red underline begins after the first $ and goes the length of the formula, with the error in the header reading, rather unhelpfully, "Errors in expression".  I have broken out the pieces and ascertained that it looks to be correct syntax.  Can anyone see what's wrong here?  Thanks so much for any guidance you can provide!

=sum({[Group 3]<[Property Description]=$::[Property Description],
[Building Description]=$::[Building Description],
[Group/Transient]=$::[Group/Transient],
[Group Block]=$::[Group Block],
[Guest Type Description]=$::[Guest Type Description],
[Guest Type Group Description]=$::[Guest Type Group Description],
[House Use]=$::[House Use],
[Owner/TimeShare]=$::[Owner/TimeShare],
capture_date={'$(v_forecast_capture)'},
[Stats Date]={'>=$(v_forecast_start_pace)<=$(v_forecast_end_pace)'}>}$(vroomnights))

[Oleg_Troyansky]

Hi @raynac ,

I can't see anything that's wrong with the syntax. It's hard to guess what the problem might be. As a work around, I'd suggest replacing this syntax with functions P(). If your current syntax causes problems (it could even be a bug in the product), then this alternative syntax might work:

sum({[Group 3]<[Property Description]=P({$}),
[Building Description]=P({$}),
[Group/Transient]=P({$}),

...

Try it and see if it solves the problem.

Cheers,

Oleg Troyansky

[raynac]

Hi Oleg,

Thanks so much for your help.  I do appreciate it!

When I try this exact code you gave me, I still get errors.  The first P does turn red, but then the red line appears at the first half of the parentheses.

Perhaps I misunderstood your instructions?  I apologize, as this is not my code... I am merely trying to remedy it.

[Oleg_Troyansky]

Hmm, it looks very strange... You did everything correctly, no worries there.

Now I spotted an issue in one of the filters, but it wouldn't give you a syntax error like this - the search condition for dates should be enclosed in double quotes and not in single quotes. But that's not the main problem. I'd try to explore the following:

1. Verify that the Alternate State [Group 3] actually exists. Maybe it was deleted.

2. Verify the contents of the variable VROOMNIGHTS - maybe there is an issue there?

3. Check how the fully expanded expression looks like at the bottom of the screen, next to the error message.

4. Check that all the fields, starting with the [Property Description], exist in the data.

5. This is a long shot, but still - what version of the software are you using? Could it be so old that it doesn't support Alternate States?

Cheers,

Oleg

[raynac]

Hi @Oleg_Troyansky 

Thank you again for all your great suggestions!

Re #5, I actually thought about the Qlikview version, after your first message.  So, I checked and I have the most recent one. 

Re #1, I checked here and Group 3 definitely still exists, unless I need to check somewhere else.

Re #2, I did copy and replace the formula for the variable into the set analysis, replacing the variable with no change.

Re #4, I checked each and every field.  Yes, all still there.

So the only one I am not sure how to do is #3. 

I am simply baffled because they were working fine at some point in the past and I don't think I have changed anything in the script or the formulae.  Plus, there are a few dozen of these set analyses in this view... this is another and the red underlining actually stops partway through this one!

I am at a loss.

[raynac]

I just discovered one more thing.  We have three different views that all rely on an extensive use of this sort of set analysis and all of them are showing the same basic error... red line starting on the first period after the first dollar sign.

[Vegar]

@raynac  Did you ever solve this?

What i tend to do when I run into problems like this is to simplify my measure. Try to identify what you can remove from this expression , but still have this issue?

Have you tried 

• =sum($(vroomnights))?
• =sum({[Group 3]<[Property Description]=$::[Property Description]>} $(vroomnights))
• Other

Consider also what you can not move out of the measure in order to avoid that issue.

It mught be easier to troubleshoot and find a solution when you have pinpointed with  an example where the problem occur.

[raynac]

Hi @Vegar,

Thanks so much for the suggestion!

 =sum($(vroomnights)) works perfectly with no errors.

If I remove the [Property Description] line, the error just "moves down" to the next condition.

That pattern continues until I remove the last condition:

and only disappears once the entire set is removed.

If I do it in reverse, remove the dates and work my way up to the Property... again, everything just stays underlined in red until I get to the top, and remove the set analysis.

Any ideas would be truly appreciated!