Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
VolkanKu
Contributor III
Contributor III

How to ignore a dimension while using SUM

Hello,

I am trying to make a report but i got stuck at getting a sum while ignoring one of the dimensions at the chart.

What i am trying to report is the top 5 materials for each period(Year, Month or Week if can use alternative or just Month if i cant) Rework Quantity of material in period / Total Stock Quantity for the period.

There are also a lot of other filters in the app from various tables that are joined by Material column. Is there any way to omit a dimension while using SUM function or can i get the result by using another function?

The main data i am using is like these 2 tables. Also the end result i need looks like the picture at the end, with ratios replacing material codes.  

TABLEX   TABLEY   TableZ 
MaterialDateStock Quantity MaterialDateRework Quantity MaterialCustpartnumber
A01.10.201936865 A01.10.2019127 AAAAA
C01.10.2019141 C01.10.201919 BBBBB
A01.09.2019103 A01.09.2019106 CCCCC
B01.09.2019390 B01.09.201927 DDDDD
C01.09.2019133 C01.09.201910 EEEEE
A01.08.201956 A01.08.20196   
B01.08.2019552 B01.08.2019256   
C01.08.201928 C01.08.201947   
A01.07.2019148 A01.07.20196   
B01.07.201916 B01.07.2019336   
A01.07.2019362 A01.07.201932   
A01.06.201990 A01.06.20191   
B01.06.2019188 B01.06.2019209   
C01.06.2019285 C01.06.201911   
D01.06.20192865 D01.06.201981   
E01.06.2019448 E01.06.201981   

 

The results i am trying to get are like these below, month year as dimension and year as alternative dimension.

Month Year     Year    
Material (1)Date (2)Total Stock Quantity for the period (3)Rework Quantity of material in period (4)

Rework Ratio

(4/3)

 Material (1)Date (2)Total Stock Quantity for the period (3)Rework Quantity of material in period (4)

Rework Ratio

(4/3)

A01.10.2019370061270,34% A2019426702780,65%
C01.10.201937006190,05% C201942670870,20%
A01.09.201962610616,93% A2019426702780,65%
B01.09.2019626274,31% B2019426708281,94%
C01.09.2019626101,60% C201942670870,20%
A01.08.201963660,94% A2019426702780,65%
B01.08.201963625640,25% B2019426708281,94%
C01.08.2019636477,39% C201942670870,20%
A01.07.2019526387,22% A2019426702780,65%
B01.07.201952633663,88% B2019426708281,94%
A01.07.2019526387,22% A2019426702780,65%
A01.06.2019387610,03% A2019426702780,65%
B01.06.201938762095,39% B2019426708281,94%
C01.06.20193876110,28% C201942670870,20%
D01.06.20193876812,09% D201942670810,19%
E01.06.20193876812,09% E201942670810,19%

 

Result.jpg

4 Replies
Kashyap_R
Partner - Specialist
Partner - Specialist

hi

try like this

Sum({<[Dimension]=>}Value)

this will nullify the filters applied on those dimensions

Hope this helps

Thanks 

Thanks and Regards
Kashyap.R
VolkanKu
Contributor III
Contributor III
Author

Hello,

Thanks for the fast reply.

I have tried using the formula you posted like this;

Sum({<[TableZ.custpartnumber]=>}[TableX.quantity]) but no success 😞 

JordyWegman
Partner - Master
Partner - Master

Hi Volkan,

Can you give a sketch of the outcome? This would make it a lot easier.

Jordy

Climber

Work smarter, not harder
VolkanKu
Contributor III
Contributor III
Author

Hello,

 

I have edited the post to include the desired results 🙂