topic Re: Income - outcome in chart pie in QlikView

Income - outcome in chart pie

Thu, 18 Oct 2012 12:35:30 GMT

Hi,

I'm currfently fighting with a:

cannot mix negative and positive in pie chart

error message.

Imagine you've got expences and income:

Cash +100

Cash -150

Cash +300

Now you could create an income pie chart for cash with 400 and one with outcome -150. But what I need is to "clear" the values - Cash: +250 (i'm sure that I'll always have a positive value or a zero).

What I tried so far:

=sum({$ <Art={Income}>} Amount) - fabs(sum({$ <Art={Outcome}>} Amount))

Doesn't work

Re: Income - outcome in chart pie

swuehl — Thu, 18 Oct 2012 12:43:03 GMT

I don't understand what you mean with "to clear the values".

Can you describe what you expect to see in the pie chart? Three pieces? What sizes?

I just don't think that a pie chart is an appropriate visualization for that.

Re: Income - outcome in chart pie

Thu, 18 Oct 2012 12:54:23 GMT

Imagine you'll create a personal finance dashboard.

You've got two accounts. Now you transfer 20'000 from AccountA to AccountB.

Each transaction has a category.

Now you want to see for what you're spending your money (over all Accounts).

Normally you'll have data like:

Account A

10.1.2012, House, -20'000

11.1.2012, Savings, -20'000

Account B

11.1.2012, Savings, +20'000

Because you only transfer(!) money from A to B it's not really a spending so the pie chart should display only House (20'000). The 20'000 are still in my hands, just transfered from one Account to another. So if you create two pie charts (one with income, one with outcome) you'll have everytime Savings with 20'000.

Guess the same problem is with an Intercompany Statements...

Re: Income - outcome in chart pie

swuehl — Thu, 18 Oct 2012 13:05:09 GMT

So you "just" need to flag the transfers and remove them from the calculations?

Do you have any informations about what Account records are just an internal transfer?

Re: Income - outcome in chart pie

Thu, 18 Oct 2012 13:29:06 GMT

why not:

sum(if (cash>0,cash,-cash))

Re: Income - outcome in chart pie

Thu, 18 Oct 2012 14:01:34 GMT

Well, for that example above it would work.

But imagine that you wanna see cleared values for your house:

You paid 2012/01 -20'000.

Your sister also lives in your house, so she'll pay you +5000

Balance for your house: -15000

You can't just remove the values, it would lead to wrong values (always depends on what you wanna see...).

That's the tricky part: I don't have any information about those groups with such records - they're automatically tagged by another system. And it could be that it will add a new category if there are enough customers earning money in that area...

@Giovanni: Guess it's the same problem (clears only null balanced values)

Re: Income - outcome in chart pie

swuehl — Thu, 18 Oct 2012 15:17:08 GMT

Sorry, I am probably still missing your setting and your requested outcome.

Could you upload some sample lines of data, and what your requested result should look like? Best as sample qvw application, excel file or INLINE script code snippet.