Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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))
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 |
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;