if Condition & Set Analysis

[h_prakash]

Hello Qlik Experts,

I have a scenario where there are three Set Analysis Expressions which need to change based on the Filter by using If conditon.

Set Analysis Expression 1 :

Sum({<Status={'Active'}, [Country Type] ={'Onsite'}>}ActiveAmount)

Set Analysis Expression 2 :

Sum({<Status={'Passive'}, [Country Type] ={'Onsite'}>}Amount*5)

Set Analysis Expression 3 :

Sum({<Status={'Active'}, [Country Type] ={'offshore'}>}Amount*8)

So I used as below

if(Getfieldselection(Filter)='Active' or Getselectedcount(Filter)=0,

Sum({<Status={'Active'}, [Country Type] ={'Onsite'}>}ActiveAmount),

if(Getfieldselection(Filter)='Passive' ,

Sum({<Status={'Passive'}, [Country Type] ={'Onsite'}>}Amount*5),

Sum({<Status={'Passive'}, [Country Type] ={'offshore'}>}Amount*8))))

When I create a KPI numbers are shwing correct but when I create a table with Granular level with project number it is taking forever to load. Sometimes table is showing calculation timeout error.

[krishna_2644]

could you please post sample qvw?

In your case, I would rather have 3 expressions on one chart and hide expressions based on your conditions.

So first expression is only visible if Filter=Active. This was you will see expression field based on your conditions and performance should be good.

Other ways are to look into script and make adjustments you you are able to use SET ANALYSIS instead of IF conditions. Expressions with IF conditions will always be slow. Avoid as much as you can.

Hope this helps.

[Digvijay_Singh]

High chances of the situation that in your data model some of the dimensions are not associated as needed and causing cross join but cannot be 100% sure without seeing data model and the chart dimensions/measures.

krishna_2644

I think he is trying in QS where we don't have measure hiding based on condition. I have been looking for this feature in QS since long.

[krishna_2644]

Or you may want to try

=if( GetSelectedCount(status)=0 or status = 'Active',

    Sum({$<status={'Active'}, [Country Type] ={'Onsite'}>}Amount) ,

    if( status = 'Passive',

        Sum({$<status={'Passive'}, [Country Type] ={'Onsite'}>}Amount*5),

          Sum({$<status={'Passive'}, [Country Type] ={'offshore'}>}Amount*8)

       )

    )

try not using getfieldselection() function. Pick(Match()) is also preferred over If() in most cases.Try Using that too.

In QS unfortunately this feature of hiding the measure isnt available yet, there are few extensions available.try using them instead.

Post the qvf that you have problem with,  in a table,the engine calculates for each and every row/column but not in a KPI where you have a single value aggregated over a set of data,so obviously there would be a latency,so we have to recheck the conditions.

[pablolabbe]

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others