Sum with respect to dimension in Pivot table

[qlik4asif]

Guys i have a requirement, Data is like

Region	Date	Product	Act	Bud
North	2017-01	Light	10	12
South	2017-01	Light-Fitting	20	22
East	2017-01	Engine	100	105
West	2017-01	Engine-Fitting	120	135
North	2017-01	Clutch	20	24
East	2017-01	Clutch Fitting	35	44

Need output like below

Need to consider		North	South	East	West
Light+Light-Fitting	Light	Sum(Act/Bud)	Sum(Act/Bud)	Sum(Act/Bud)	Sum(Act/Bud)
Engine+Engine-Fitting	Engine	Sum(Act/Bud)	Sum(Act/Bud)	Sum(Act/Bud)	Sum(Act/Bud)
Clutch+Clutch-Fitting	Clutch	Sum(Act/Bud)	Sum(Act/Bud)	Sum(Act/Bud)	Sum(Act/Bud)

Thanks in advance

[devarasu07]

Hi,

what is the issue here?

Dimension:

Product, Region

Measure:

sum(Act)/sum(Bud)

Data:

load *, pick(WildMatch(Product,'*Clutch*','*Engine*','*Light*'),'Clutch','Engine','Light') as ProductShort;

LOAD * INLINE [

  Region, Date, Product, Act, Bud

    North, 2017-01, Light, 10, 12

    South, 2017-01, Light-Fitting, 20, 22

    East, 2017-01, Engine, 100, 105

    West, 2017-01, Engine-Fitting, 120, 135

    North, 2017-01, Clutch, 20, 24

    East, 2017-01, Clutch Fitting, 35, 44

];

[sumanta12]

Calculated dim:

=IF(WILDMATCH(PRODUCT,'*CLUTCH*'),'CLUTCH',

IF(WILDMATCH(PRODUCT,'*LIGHT*'),'LIGHT',

IF(WILDMATCH(PRODUCT,'*ENGINE*'),'ENGINE',)))

Expression:

SUM(ACT/BUD)

[qlik4asif]

Hi Devarasu,

Thanks for the reply

The final table which you have highlighted

in Clutch it should consider both clutch and clutch-fitting

Sum of Clutch+Clutch Fitting

Same for other products too

[qlik4asif]

Hi Sumnta,

Thanks for the reply

in Clutch it should consider both clutch and clutch-fitting

Sum of Clutch+Clutch Fitting

Same for other products too

[amarnath_krishn]

LOAD *,
SubField(SubField(Product,'-',1),' ',1) as ProductGroup;
LOAD Region,
Date,
Product,
Act,
Bud
FROM
SourceFile;

Then use ProductGroup in your chart presentation.

[sasiparupudi1]