If statement error with set analysis

[#1]

Hi all,

I have a lot of expressions in one expression and I would like to reduce the amount of code. The reason is that I am making a kpi dashboard.

Suppose: my KPI is: count the new projects (and show it per risk category). In short 1 have one expression which is evaluated in 3 expressions: Risk high, medium and low. So the only difference is the Risk = High is in the High expression and risk = medium in the medium expression. (per KPI)

My code that works is:

if([Tech Name]=45,  \\No extra condition here

          num(

                    sum(

                              aggr(

                                        if(

                                                  sum({< ctr_source={'ENG'}, EA.Z_Risk_Grade={'H'}, hours_month = {">=$(v45_date_from) <=$(vReportingDate)"}, engagement_max_end_date_empty_flag = {1}  >} timeAmount) =0

                                        ,1)

                              ,engagementId, [KPI name],EA.Z_Risk_Grade )

                    )

          ,'#.##0')

But if I would to put the risk grade in the top if statement, I get a - in the resulting table, like this:

if([Tech Name]=45, EA.Z_Risk_Grade='H',

          num(

                    sum(

                              aggr(

                                        if(

                                                  sum({< ctr_source={'ENG'}, hours_month = {">=$(v45_date_from) <=$(vReportingDate)"}, engagement_max_end_date_empty_flag = {1}  >}                               timeAmount) =0

                                        ,1)

                              ,engagementId, [KPI name],EA.Z_Risk_Grade )

                    )

          ,'#.##0')

I have multiple tables, and I have read in the ref. manual that set analysis won't work properly when aggregating over multiple tables.

Could it be the problem that because Riskgrade is in a different table than the engagement table, and the hours (time amount) table?

Thank you very much!

background:

• tech name: just a number which is coupled to a kpi description

[jonathandienst]

Martijn

You have a syntax error in the second expression. It should read:

if([Tech Name]=45 and EA.Z_RiskGrade='H', ....

Hope that helps

Jonathan

[#3]

Sorry! I made a copy paste error in this forum. In Qlikview it was correctly put as you suggested, but no results. Strange, don't you think?

[#4]

Hi Martijn,

The reference manual is correct. It also states not to use set analysis over fields in different tables. my advice to u wud be to make a single table for the fields and then apply. also double check ur formula for syntax errors.

Hope this helps.

Rgrds,

Abhinava

[#5]

Hi,

But that is really a shortcoming of set analysis right? Because an If statement work great at all times, and with set analysis you might get wrong results.

Because I am working with tables like, engagements, hours, assessments which are all related to each other in most of my queries. I can't join them because then the amounts will be incorrect.... So I have to keep separate tables.

mmm, I think I have to create 1 big table then, create flags etc....  Or use the slow if statements....

Thank you very much.

[jonathandienst]

Martijn

The manual warns against using the set operators with multiple tables, not set analysis per se. The set operators are the union +, exclusion -, intersection * and xor / operators. I have not actually experienced any problems with them myself.

I think you can use set analysis quite freely across multiple tables, assuming that there is some association between the tables. On large data sets, the use of if statements in expressions can cause serious performance issues while set analysis performs far better.

Sometimes it is necessary to modify your data model as your needs change, but I would try set analysis first.

For your original post, have you tried removing the risk grade field dimension from your aggregate? Also, if you post your app or a sample, it may help to understand the problem.

Regards

Jonathan