topic Re: Multiplication in Pivot Chart works only for non-empty dimension in QlikView

Multiplication in Pivot Chart works only for non-empty dimension

Wed, 18 Jul 2012 10:45:48 GMT

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

Re: Multiplication in Pivot Chart works only for non-empty dimension

swuehl — Wed, 18 Jul 2012 10:50:25 GMT

Have you tried this?

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

Re: Multiplication in Pivot Chart works only for non-empty dimension

Wed, 18 Jul 2012 11:35:08 GMT

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

Re: Multiplication in Pivot Chart works only for non-empty dimension

swuehl — Wed, 18 Jul 2012 13:12:07 GMT

What about

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

edit:

Or maybe fixing your missing ServiceGroup problem?

Re: Multiplication in Pivot Chart works only for non-empty dimension

Wed, 18 Jul 2012 15:26:30 GMT

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.

Re: Multiplication in Pivot Chart works only for non-empty dimension

swuehl — Wed, 18 Jul 2012 15:37:49 GMT

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:

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

];

Re: Multiplication in Pivot Chart works only for non-empty dimension

Thu, 19 Jul 2012 07:30:11 GMT

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.

Re: Multiplication in Pivot Chart works only for non-empty dimension

swuehl — Thu, 19 Jul 2012 09:18:45 GMT

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

Re: Multiplication in Pivot Chart works only for non-empty dimension

Thu, 19 Jul 2012 14:49:14 GMT

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?