5 Replies Latest reply: Apr 21, 2010 12:58 PM by John Witherspoon

# How I get the simple values out of an aggregation?

Hello together,

I am a Collage Student and really unexperienced in QlikView. But still I hope someone helps me out a little bit.

Here ist my Problem.

I want to make a simple bar diagramm with the data (Sold shoes):

jan 15

feb 20

Mar 10

But my Problem is, that I only have the aggregated values (Sold shoes)

jan 15

feb 35

Mar 45

the normal form would be: sum (total shoes)

but now i need is something like:

sum(total shoes) -sum(month-1 total shoes)

I hope you understand my problem.....sorry, my english is kind of limited.

I would be very glad if someone could help me out!

Greetings

-Nils-

• ###### How I get the simple values out of an aggregation?

Can you convert back to simple data during the load? You can always aggregate again in a chart later if you need to.

Month
,rangesum(AggregatedShoes,-previous(AggregatedShoes)) as Shoes
INLINE [
Month, AggregatedShoes
jan, 15
feb, 35
Mar, 45
];

• ###### How I get the simple values out of an aggregation?

Hi Nils,

If i understood your question properly then below is the suggestion,

if you want to accumulate the sale with you previous month sales; In the expression tab, select your expression (Sum(Sales)) and check the box "Full Accumulation" beneath the expression.

Regards

• ###### How I get the simple values out of an aggregation?

@ Rajesh Jeyaraman

no, i exactly need the opposite.

i have the accumulated data and want a chart without the accumulation

• ###### How I get the simple values out of an aggregation?

at John Witherspoon

Month
,rangesum(AggregatedShoes,-previous(AggregatedShoes)) as Shoes

that works fine!

I dont unserstand the inline expression but that code above works fine.

But now I Have the problem that i got differnt salesmen.

My Data looks like

Month Shoes Salesman

Jan 10 Name1

Feb 15 Name1

Mar 15 Name1

Jan 5 Name2

Feb 30 Name 2

Mar 35 Name 2

How do I get the load expression saparated by salesmen?

• ###### How I get the simple values out of an aggregation?

AggregatedShoes - if(Salesman=previous(Salesman),previous(AggregatedShoes),0)

The INLINE statement was just a way of loading some hardcoded data. It allows us to test that something works before hooking it up to the real data source. In this case, I don't have access to your data source, so it was a practical way of reproducing your data. It's also something that anyone could cut and paste to see that it works. Previous() looks at the row before this one in the source table. Rangesum(A,B) is just like doing A+B, except that if either is null, it treats them as 0, so that you still get the other value. That was necessary since the previous(AggregatedShoes) row on row 1 would return null. We don't need the rangesum() above because we're handling nulls differently. Here, if we're on row 1, the previous(Salesman) will be null, which is not the same as the current row's Salesman, so we return 0 explicitly. We do the same when the Salesman changes. But as long as we're within the same Salesman, each row subtracts the previous aggregation.