Multiplication in Pivot Chart works only for non-empty dimension

[#1]

Hi @all,

In my Pivot Chart I use three dimensions (account group, account sub group and service group). Account group and account sub group are vertical dimensions, service group is horizontal dimension. That means values of account group and account sub group are in lines and values of service group are in colums. Now I want to multiply my figures for certain account groups by -1. This works for all service groups except the last column. The last column shows figures without any service group.

Attached you'll find a small example. Could someone please explain to me why the last column isn't multiplied by -1 and how can this be solved?

Kind regards

[swuehl]

Have you tried this?

=Sum(If(AccountGroup = 'I',-1,1)*Amount)

[#3]

This works in my small example. But in real dashboard this leads to complete different figures.

[swuehl]

What about

=Sum(Amount) * If(aggr(nodistinct AccountGroup,AccountNo) = 'I',-1,1)

?

edit:

Or maybe fixing your missing ServiceGroup problem?

[#5]

Same result. Every figure in the last column is multiplied by -1 instead of just the ones of special account groups.

In the system it's possible to leave ServiceGroup blank. So, that's not an option.

[swuehl]

Do you have any idea why the suggested solutions (I assume the second works also only your side) work in your sample but not in your real app? Any noticeable differences in the data model?

Would be great if you could adapt your sample in a way that we can reproduce the behaviour also with my suggested expressions.

And again, it might be worth to think about introducing a service value for a 'not entered' or 'unknown' ServiceGroup (in your script / QV model only, not touching the source system's data). This should solve your problem right away.

edit:

Like

Multifacts:

LOAD AccountNo, if(len(trim(Service)),Service, 'unknown') as Service, Amount Inline [

  AccountNo, Service, Amount

  1, A, -100

  1, D, -50

  1,  , -75

  1, B, -60

  2, B, 100

  2, C, 50

  2, E, 40

  2,  , 25

];

Service:

LOAD * Inline [

  Service, ServiceGroup

  A, S1

  B, S1

  C, S1

  D, S2

  E, S2

  unknown , unknown

];

Account:

LOAD * Inline [

  AccountNo, AccountGroup, AccountSubGroup, Sort

  1, I, S, 1

  2, C, D, 2

];

[#7]

I can't see no significant difference.

I mean I'm able to reproduce it in my simplified example. Although I see no reason why it shouldn't work in that example, it does only work if I use another way/workaround. Is there any explanation to this behaviour? Maybe it's possible to set empty Service Group to a certain value. I have to discuss it with my colleagues. But that's then another workaround. I mean I really see some advantages in using QlikView but the deeper I go into detail the more I see unexpected behaviour and workaround solutions.

[swuehl]

I wouldn't call assigning a value to 'unknown' or 'not set' Service/ServiceGroup a workaround. You actually want to analyze the amounts assigned to these (if not, you could enable 'suppress NULL' in dimension tab, so I wouldn't bother creating a value for these cases.

I agree that it is not intuitive that you can't just call the dimension value for the first dimension like this, I believe (but that's based on my limited knowledge on how QV actually works in detail) that this is due to the fact that you actually don't have a link between the Service table and the Multifacts table in case of NULL.

So QV can't unambigously resolve the AccountGroup following the links from Service via Multifacts to Account table.

I believe you can work around this using similar expressions like a posted above, forcing QV to find a way. But the solution I would favor is, as said, cleaning your data model, i.e. creating the missing link if you need to analyze this aspect.

Instead of 'unknown', you could call the Link '-', so you or your colleagues even wouldn't notice a difference to today's implementation (except the sign of the amount should be correct).

Another possibility might be to correct the sign of the amounts (for example in the load script), if possible.

Hope this helps,

Stefan

[#9]

I see your point, I think best solution is to set-up some kind of service group for empty/NULL values.

But in that case I've got another issue. The amount is not linked to Service Group by Service Code. It's linked by some kind of order. That means there's Amount and OrderID in Multifacts table and OrderID and ServiceGroup in another one. That means it would be possible to set-up ServiceGroup for internal orders (these haven't got any ServiceGroup). But it wouldn't be possible to set-up ServiceGroup if the Amount isn't linked to any OrderID. Any idea how this could be solved without moving ServiceGroup column from Order table to Multifacts table?