Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

SYSTEM MAINTENANCE: ** Thurs., Sept. 19, 1 AM ET,** Platform will be unavailable for approx. 60 minutes.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Re: Optimize / Fix calculation

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Martin22

Partner - Contributor III

2020-06-24
05:43 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Optimize / Fix calculation

Hi all,

I'm currently trying to implement a calculation, but it's not working the way I want.

The point is to have the calculation work as a KPI.

The problems I encounter are :

-I have a dozen "sub_categories" values to put in the calculation, and I would like to optimize the way it is written, while keeping how it works.

-The KPI is supposed to adjust to what data are selected : currently, the result stays the same whatever filter I choose.

Here is the calculation :

((sum({<[sub_category] = {'1'}>}[Flag]))

/ (sum({<[category] = {'A','B'}>}[Flag])))

*

((sum({<[sub_category] = {'1'}>}[measure])))

+

((sum({<[sub_category] = {'2'}>}[Flag]))

/ (sum({<[category] = {'A','B'}>}[Flag])))

*

((sum({<[sub_category] = {'2'}>}[measure])))

+

((sum({<[sub_category] = {'3'}>}[Flag]))

/ (sum({<[category] = {'A','B'}>}[Flag])))

*

((sum({<[sub_category] = {'3'}>}[measure])))

Can you help ?

Thanks,

Martin.

873 Views

1 Solution

Accepted Solutions

jsebrightqi

Partner - Contributor II

2020-06-24
09:39 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

*= in the set analysis may help you to get different answers if you're making selections on the subcategories.

Thinking about how to simplify the expressions though-

It looks like you've got the following sum: (simplified a bit)

sum(Flag1) * sum(Measure 1) / sum(FlagAB)

+ sum(Flag2) * sum(Measure 2) / sum(FlagAB)

...

That could be simplified into

(sum(Flag1*Measure1)/sum(FlagAB) + sum(Flag2 * Product2) ) / sum(FlagAB)

Which can be simplified into

(sum(Flag1*Measure1) + sum(Flag2 * Product2) ) / sum(FlagAB)

Setting a variable with a parameter will allow you to simplify the writing of this a bit

Let vSubCatProduct = (sum({<[sub_category] = {'$1'}>} [ Flag] * [measure]));

Then your expression would be:

( $(vSubCatProduct(1))+$(vSubCatProduct(2)).... ) / (sum({<[category] = {'A','B'}>}[Flag])))

It's optional whether you put the (sum({<[category] = {'A','B'}>}[Flag]))) part into the variable or add it in the expression -if you display the subcategory/AB calculations separately then it would be better to do it in the variable.

831 Views

4 Replies

Mauritz_SA

Partner - Specialist

2020-06-24
09:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Martin22

By using set analysis you are effectively hard-coding the filter values for those fields. You can have a look at this blog article by @stevedark which I found really helpful on using Star Equals in set analysis which I think can assist you.

Hope it helps.

Regards,

Mauritz

jsebrightqi

Partner - Contributor II

2020-06-24
09:39 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

*= in the set analysis may help you to get different answers if you're making selections on the subcategories.

Thinking about how to simplify the expressions though-

It looks like you've got the following sum: (simplified a bit)

sum(Flag1) * sum(Measure 1) / sum(FlagAB)

+ sum(Flag2) * sum(Measure 2) / sum(FlagAB)

...

That could be simplified into

(sum(Flag1*Measure1)/sum(FlagAB) + sum(Flag2 * Product2) ) / sum(FlagAB)

Which can be simplified into

(sum(Flag1*Measure1) + sum(Flag2 * Product2) ) / sum(FlagAB)

Setting a variable with a parameter will allow you to simplify the writing of this a bit

Let vSubCatProduct = (sum({<[sub_category] = {'$1'}>} [ Flag] * [measure]));

Then your expression would be:

( $(vSubCatProduct(1))+$(vSubCatProduct(2)).... ) / (sum({<[category] = {'A','B'}>}[Flag])))

It's optional whether you put the (sum({<[category] = {'A','B'}>}[Flag]))) part into the variable or add it in the expression -if you display the subcategory/AB calculations separately then it would be better to do it in the variable.

832 Views

stevedark

Partner Ambassador/MVP

2020-06-24
12:32 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Mauritz - thanks for sharing that post.

Martin22

Partner - Contributor III

2020-06-25
04:49 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

Thank you for your answers, I'm going to try and see if I obtain what I'm looking for with both the set analysis and simplification, and I'll update the post.

Regards,

Martin.

Community Browser