Expression how to get total count if dimension is identical

[#1]

Hello,

If i have a dimension Sales_team and then the Count_Sales and Total_sales_value

S_Team     Count     USD

A_1          15     5000$

A_2          10     2000$

A_2          10     2000$

A_3          30     1000$

What i would need is a temporary table which would display the total value in the row of the identical dimension

A_1          15     5000$

A_2          20    2000$

A_2          20     2000$

A_3          30     1000$

[Jason_Michaelides]

In the script I'd probably do it like this:

Data_temp:
LOAD * INLINE [
S_Team,Count,USD
A_1,15,5000$
A_2,10,2000$
A_2 ,10,2000$
A_3 ,30,1000$
];
Map_Total:
MAPPING LOAD
S_Team,
SUM(Count)
RESIDENT Data_temp GROUP BY S_Team;
Data_Final:
LOAD
     S_Team,
     Count,
     ApplyMap('Map_Total',S_Team,0)     AS     SumCount,
     USD
RESIDENT Data_temp;
DROP TABLE Data_temp;

You could probably also achieve it in your chart objects by using the TOTAL qualifier e.g. SUM(TOTAL Count), although I always prefer putting known calculations in the script for performance reasons.

Hope this helps,

Jason

[#3]

Hi Jason,

Thanks for the reply.. If I would want to load this from an external table (excel)? Sorry, im quite new at this.

Could you also provide how you would create this in the expression? Sum(total **) doesnt seem to work.

Thanks.

[Jason_Michaelides · Accepted solution]

Just replace the INLINE table load with a standard Excel load script. If you're not sure how to do this I suggest you search the community/QlikView site for "build your first QlikView app" or watch the Excel video here - http://community.qlik.com/docs/DOC-1986

As for the expressions, build a straight table chart with e.g. S_Team and USD as the dimensions. Add two expressions: SUM(Count) and SUM(TOTAL <S_Team> Count) and see the difference.  TOTAL removes all the dimensionality of the chart, and <> puts selected dimensions back in.

Hope this helps,

Jason