topic Problem with SUM in QlikView

Problem with SUM

sparur — Wed, 02 Feb 2011 08:52:33 GMT

Hello, guys.

I have some problems with such situation:

I have simple table:

and I want to get sum of Sales and Plan values by Company, like this:

Company, Fields - 2 dimension, Result - expresson.

BUT the main difficulty is that I want to achieve this with one expression!

I created new field with name 'Fields', which contain values with all my Key Figures ('Sales' and 'Plan'),

and I want to use something like this: sum(Fields), but I can't achieve that Fields transform to values,

i.e.
for lines, where Fields = 'Plan' I want get expression - sum(Plan),

for line where Fields = 'Sales' - get sum(Sales)

Can somebody help me?

I know, that can use crosstable function (instead of new field 'Fields') for transforming my source table, but I try to avoid it.

look at my example in attachment.

Problem with SUM

sparur — Thu, 03 Feb 2011 07:45:36 GMT

anyone have any ideas?

Problem with SUM

Thu, 03 Feb 2011 09:13:36 GMT

Crosstable is an elegant solution here. I would use that.

Problem with SUM

sparur — Thu, 03 Feb 2011 11:13:21 GMT

Hello, Matt.

yes, I know that is one of solutions, but I want to avoid it. Because it very "hard" statement. In my real project I have fact table about 5 million of rows and crosstable process take a long time. I want to find more elegant solution(without crosstable), or I want to be sure that crosstable is unique solution.

Problem with SUM

srinivasa1 — Thu, 03 Feb 2011 11:16:01 GMT

Even I too feel Cross tabel can helpn this case.

Problem with SUM

sparur — Thu, 03 Feb 2011 11:26:06 GMT

Hello, srinivasa1

I know about crosstable, thanks. but I want to find other solution.