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

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- calculation with tables

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

Costa

Contributor III

2021-06-11
01:05 PM

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

calculation with tables

Hey there,

I'm more or less new and have a hopefully pretty simple quetsion.

I have a basic table (step one) which contains data of earnings and costs. The values in this table are all positve.

These 4 accounts can also be grouped as "profit contribution". Of course, this is earnings-costs. So i wrote a statement in step two in the set-analysis ( If([Account type] ='costs', Sum(Value)*-1,sum(Value)) )....at first, it seems to work just fine, because the value 25 is now -25.

however, when trying to aggregate it by only showing "profit contribution" it still gives me the wrong value.

Can I solve this problem by any formula in the set analysis or do I have to do the value*-1-calculation in the scripting.

Many thanks for your help!!

1 Solution

Accepted Solutions

sunny_talwar

MVP

2021-06-11
02:09 PM

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

Try this

```
RangeSum(
Sum({<[Account type] = {'costs'}>} Value)*-1,
Sum({<[Account type] = {'earnings'}>} Value)
)
```

3 Replies

sunny_talwar

MVP

2021-06-11
02:09 PM

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

Try this

```
RangeSum(
Sum({<[Account type] = {'costs'}>} Value)*-1,
Sum({<[Account type] = {'earnings'}>} Value)
)
```

Costa

Contributor III

2021-06-11
02:13 PM

Author

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

Many thanks, works perfectly fine!!

I just tried

Sum(If([Account type]='costs',Value*-1,Value))

which also kind of works 🙂

THanks Sunny_talwar :)!

588 Views

chriscammers

Partner - Specialist

2021-06-11
03:56 PM

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

It looks like you are doing a financial report and you will likely be adding in hundreds of accounts. I would suggest creating a table in the load script that would allow you to set the sign for each account. You might need to create different columns for other measures depending on how they are handled. then your expressions in your chart can be something like

```
//Put this in your load script
AccountMultipliers:
Load * Inline [
Account type,Multiplier
cost,-1
earnings,1
];
//use this in a chart
Sum(Value * Multiplier)
```