How to represent using an expression using Set analysis

[Anonymous]

Hi,

I'm having some problems setting icons using the following expression in Qlik Sense using the Simple KPI object:

if(sum([Qtr 2] - [Qtr 1])<0, 'caret down icon', if(sum([Qtr 2] - [Qtr 1])=0, 'caret right icon', 'caret up icon'))

It always appears to show the caret up icon (the last value).  should I use Set analysis to do this properly?  If so what should the expression look like?

I'm also very keen to learn Set analysis in expressions so any resources I can look at will be much appreciated.  I've looked at a couple of examples and it looks complex at face value.

Any help will be gratefully appreciated.

Kind regards,

Ozzie

[sfatoux72]

Hi Ozzie,,

What is [Qtr 1] and [Qtr 2] for you ? Is it not KPI?

Normally, you need to write something like that:

if(sum({$<Year={$(=max(Year))}, Qtr={2}>}myKPI) < sum({$<Year={$(=max(Year))}, Qtr={1}>}myKPI),

  'caret down icon',

  ifsum({$<Year={$(=max(Year))}, Qtr={2}>}myKPI) = sum({$<Year={$(=max(Year))}, Qtr={1}>}myKPI),

    'caret right icon',

    'caret up icon'

  )

)

Where myKPI is the KPI that you want to analyse and Year and Qtr are two time dimension fields linked to your KPI

[Anonymous]

Hi Sebastien,

[Qtr 1] and [Qtr 2] are columns containing quarterly totals for various line items (that are in rows).

What I'm looking to do is represent the movement between each quarter using a suitable icon based on the variance between the 2 quarters.

If you can assist with this, that will be fantastic.

I like your example and will look at that further too.

Thank you kindly Seb,

Ozzie

[chhavi376]

Hi Ozzie,

I am not very sure that how your data file exactly looks like,

So I am attaching two scenarios with their data sources.

One, in which QTR 1 and QTR 2 are two different columns and second, in which QTR 1 and QTR 2 are dimension values of one column.

You aren't using set analysis in your current solution, as you are considering that QTR 1 and QTR 2 are 2 different columns in your database.(Sheet 1 of the excel file attached.)

Please have a look at the attached QV and the data source.

Thanks.

[sfatoux72 · Accepted solution]

Hi Ozzie,

I well understand that you would represent the movement between each quarter using a suitable icon based on the variance between the 2 quarters.

But having the same KPI in more than one field is not correct in terme of design. Normally, you need to have one KPI and one dimension to represent that.

Before you change your design, you could try with this expression :

if(sum([Qtr 2]) < sum([Qtr 1]),
  'caret down icon',
  if(sum([Qtr 2]) = sum([Qtr 1]),
    'caret right icon',
    'caret up icon'
  )
)

For information, you must not directely add or substract two fields, because if one is null the result will be null.

• Instead of : Sum( [Qtr 2] - [Qtr 1] )
  • Use : Sum([Qtr 2]) - Sum([Qtr 1])
  • Or : Sum( RangeSum([Qtr 2], -[Qtr 1]) )
  • Or : Sum( Alt([Qtr 2], 0) - Alt([Qtr 1],0) )

[Anonymous]

Hi Sebastien and Chhavi,

Thanks very kindly - these work for me.

I really appreciate your time to respond.

There also appears to be a bug in the Simple KPI extension where formulas such as ours are used to display a particular icon image and/or a color based on a dimension value.  A fellow Qlik colleague has raised this issue with the developer.

Take care,

Ozzie