sum in Pivot table

[#1]

Hello Community,

I'm quite new to QlikView and I have been working on this problem the whole day. I can't solve it. So maybe one of you can tell me where I made the mistake.

I have two fact tables:

1) SalesEntries

ItemNo SalesAmount
-------------------------------------
1000 500.-
1000 250.-
1000 250.-

2) ReturnEntries

ItemNo ReturnAmount ReasonCode
-----------------------------------------------------------
1000 100.- BROKEN
1000 100.- COLOR

I want to calculate a return ratio and display it in a pivot table. The table looks like this:

ItemNo ReasonCode SalesAmount ReturnAmount Ratio

If I do not expand the reason codes everything is alright. The table looks like this:

ItemNo ReasonCode SalesAmount ReturnAmount Ratio
---------------------------------------------------------------------------------------------
1000 - 1000.- 200.- 20 %

If I expand them it looks like this:

ItemNo ReasonCode SalesAmount ReturnAmount Ratio
---------------------------------------------------------------------------------------------
1000 - 1000.- 0.- 20 %
BROKEN 0.- 100.- n/a
COLOR 0.- 100.- n/a

The problem now is that I understand why QliView does this, but I want the table to look like this:

ItemNo ReasonCode SalesAmount ReturnAmount Ratio
---------------------------------------------------------------------------------------------
1000 - 1000.- 200.- 20 %
BROKEN 1000.- 100.- 10 %
COLOR 1000.- 100.- 10 %

This would give the user the possibilty to have a closer look what the ratio is on the different Reasons related the the total ReturnAmount. But I cannot figure out what formula I should use. I tried using
= agg (sum (SalesAmount) ItemNo) because I expected it would calculate the SalesAmount for ItemNo 1000 for each reason. But that didn't work. I also experimented with top and bottom. Didn't work out either.

Any one of you an idea how to solve this? Or is there a fundamental error in what I want to do here?

Thanks in advance.

Alex

[johnw]

It's working just like you want it for me, so I probably have something set differently than you, and the difference is probably your answer. That or your real data model is somewhat more complicated than your example, which also seems likely.

[#3]

Wow, thanks alot.

That didn't solve the problem, but I managed to reproduce my problem in your example. You find in included here.
What I forgot was that there is also a connecten between the two tables with the field PostingDate. That of course I need if the user wants to see the ratio for a special month or day.

Regards

Alex

[johnw]

Well, not that this solves your problem, but rather than have a synthetic key for the item and date, I'd probably just concatenate these both into a single table. That doesn't in and of itself fix anything, but to me is more clear. Here is a step in the right direction. I'll try to get back to it, but I need to run off to a meeting.

[#5]

I Can't Believe It's Not Butter!

It's working! After your tip with the formula

sum(total <ItemCycle> Verkaufsbetrag)

Not even that the values for SalesAmount are right. For the Returnvalue I used

if

 (ISNULL(Reasoncode),-sum(total <ItemCycle> ReturnAmount),-Sum(ReturnAmount))

and these value are ok, too. I can even calculate a ratio for the number of shipments and returns using

if (ISNULL(Reasoncode),count(total <ItemCycle> ReturnAmount),count(ReturnAmount))

and

count(total <ItemCycle> SalesAmount)

with dividing the two values. You made me very happy. Thank you very very much.
I have to run off to bed now cause it's nearly 11 p.m. in Germany.

Thanks again and good night from Berlin.

Alex