How to put the total sales value specific Country value qlik sense pivot

[Naps_230]

Hi Team,

i have some requirement  below like pivot in EXCEL, here we have to put total sales value 1228 into ENG for country field for another table, want show like out put  table below. How to achieve this qlik sense. shall i need to create separate modeling for Country field.

Source table:

Country	Group	Sales
IND	A	100
AUS	A	200
ENG	A	250
IND	B	110
AUS	B	268
ENG	B	300

Out put:

Sum of Sales	Group
Country	A	B	Grand Total	C
AUS	200	268	468
ENG	250	300	550	1228
IND	100	110	210
Grand Total	550	678	1228	1228

[hic]

I assume you want to do this in a chart and not in the script. Then you should use

Dimension: Country
Measure 1: Sum(Sales)
Measure 2: If(Country='ENG',Sum(total Sales))

[Naps_230]

Hi Hic,

Thanks for help, i need one more help on that,i used your script, is working for total level,not in group level.

my out put should be like that below,

ENG value for 2nd measure : Group A ,B Should total of total of all 3 country  like below out put. How to achieve this?

out put:

Sum of Sales	Group
Country	A	B	Grand Total	A	B	Grand Total
AUS	200	268	468
ENG	250	300	550	550	678	1228
IND	100	110	210
Grand Total	550	678	1228	550	678	1228

[vinieme12]

Associate Country value "ENG" with all rows in the dataset by creating a new grouped dimension as below

temp:
load *,Country&'-'&Group as CountryGroup_Dim  inline [
Country,Group,Sales
IND,A,100
AUS,A,200
ENG,A,250
IND,B,110
AUS,B,268
ENG,B,300
];

temp_dim:
Load Country,Group,CountryGroup_Dim
Resident temp;

/** Group Total **/
Load Country,'Group Total' as Group,CountryGroup_Dim
Resident temp;

/** Group C **/
Load 'ENG' as Country,'C' as Group,CountryGroup_Dim
Resident temp;

left Join(temp)
Load * Inline [
Group,so_Group
A,1
B,2
Group Total,3
C,4
];

Drop field Country,Group from temp;


exit Script;