Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi !
I have 2 tables :
[CODE]
Sales:
LOAD * INLINE [
Prod, Qtt
A, 10
A, 10
B, 15
C, 20
];
[/CODE]
and
[CODE]
Prices:
LOAD * INLINE [
Prod, Price
A, 0.90
B, 1
C, 0.50
];
[/CODE]
I want to display a chart with the total Qtt and the total Qtt*Price
The total Qtt is OK, but the total "Qtt * Price" displays a "-".
Is there a way to display this total without make the multiplication while loading ? (I have in fact more dimensions that are not compatible with an inline multiplication)
This looks like a problem I encountered before because QlikView cannot figure out what to display in the Totals. Do you want the Totals to display SUM or AVG or something else.You have to use the AGGR() function to solve the problem. I think the syntax is
SUM(AGGR(SUM(Qtt)*ONLY(Price), Prod))
OR
AVG(AGGR(SUM(Qtt)*ONLY(Price), Prod))
There is a very good film clip showing how to do AGGR() functions - I will try to find it and post it on the forum
Probably because you are using a Pivot table rather than a straight table, for some reason you cannot control the totals on a pivot table.
What if I want to display sub-totals, instead of only totals ? (if I have more than 1 dimension)
Let's say that I have a dimension "category" :
Sales:
LOAD * INLINE [
Prod, Qtt
Orange, 10
Orange, 10
Apple, 15
Salad, 20
];
Prices:
LOAD * INLINE [
Prod, Price
Orange, 0.90
Apple, 1
Salad, 0.50
];
Categories:
LOAD * INLINE [
Prod, Categ
Orange, Fruit
Apple, Fruit
Salad, Vegetable
];
I would like the subtotal by category (for displaying a bar chart for instance) :
This looks like a problem I encountered before because QlikView cannot figure out what to display in the Totals. Do you want the Totals to display SUM or AVG or something else.You have to use the AGGR() function to solve the problem. I think the syntax is
SUM(AGGR(SUM(Qtt)*ONLY(Price), Prod))
OR
AVG(AGGR(SUM(Qtt)*ONLY(Price), Prod))
There is a very good film clip showing how to do AGGR() functions - I will try to find it and post it on the forum
Thank you !
I can have my total with the "aggr" function.