topic Re: How to 'Group By' a Dimension in Straight Table? in QlikView

How to 'Group By' a Dimension in Straight Table?

dmohanty — Thu, 02 Jul 2015 16:33:42 GMT

Hi All,

I have to display a Straight Table like this below:

ID	Plant	Sales by Plant	Sales by ID
100	A	50	220
100	B	70	220
100	C	60	220
100	D	40	220
200	A	30	190
200	B	60	190
200	C	80	190
200	D	20	190

To Calculate the Sales by Plant: I have some formula written in Expressions (for example - SUM(Sales_1) + SUM(Sales_2)) and giving the Correct Results based on Plants.
To Calculate the Sales by ID: How can I use the same expression, but has to be GROUP BY the Dimension 'ID' , so that it would display the results like above. It has to Sum of all the Sales for all Plants for that particular ID.

I tried using the AGGR(), like this: AGGR( SUM(Sales_1) + SUM(Sales_2) , ID) , but the result is like in this format in the chart. It's not filling up the Cells in the Chart.

D	Plant	Sales by Plant	Sales by ID
100	A	50	-
100	B	70	-
100	C	60	220
100	D	40	-
200	A	30	-
200	B	60	-
200	C	80	190
200	D	20	-

Regards!

Re: How to 'Group By' a Dimension in Straight Table?

Gysbert_Wassenaar — Thu, 02 Jul 2015 16:35:34 GMT

rangesum(sum(total <ID> Sales_1),sum(total <ID> Sales_2))

Re: How to 'Group By' a Dimension in Straight Table?

ramoncova06 — Thu, 02 Jul 2015 16:49:55 GMT

your aggr is fine, you just need to add the nodistinct, so it be included on every row

AGGR( nodistinct SUM(Sales_1) + SUM(Sales_2) , ID)

Re: How to 'Group By' a Dimension in Straight Table?

dmohanty — Fri, 03 Jul 2015 02:34:56 GMT

Thank you very much Gysbert and Ramon!