Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Vivek12
Contributor II
Contributor II

Set Analysis to calculate value independent of one of the multiple dimensions in straight table

Hey,

I am stuck with something and would be helpful if somebody can help me on this

I have a data set like this (just a sample-actual data has much more information to it)

Plant Name Department Name Parameter Type Value
IMT Manesar A X 5200
IMT Manesar A Y 3000
IMT Manesar B X 2000
IMT Manesar B Y

4000

 

IMT Manesar Common Z 1000
IMT Manesar Common Z 1400
IMT Manesar Common Z 1600

 

I want to show this data in a table chart like this

Plant Name Department Name Performance (based on Z values only)
IMT Manesar A 4000
IMT Manesar B 4000

 

Basically, it is calculating the total "Z" parameter type for "common" department name and showing it against other departments (A & B) in a table.

I have tried to expression using TOTAL & Aggr to sum the values without getting affected by the table dimension (A or B) but in that case it is also ignoring the plant name and just giving me the total sum across all the plant names in my data in all the rows

Can anyone help on how to write the expression independent of only 1 dimension and not others?- I have also used "Department Name=" in my expression in my set analysis but it is still not giving my any values against the rows with department name dimension as A or B.
If you can share the expression as well which I can test on my data, that would be helpful
Thanks in advance !

Labels (3)
3 Replies
AronC
Partner - Creator II
Partner - Creator II

@Vivek12 
Use Aggr.
You could use this expression as a start and refine.

sum({1<[Department Name] = {'A','B'}>}
aggr(Sum({1<[Parameter Type] = {'Z'}>}Total <[Plant Name]> Value)+ Sum({1<[Department Name] = {'A','B'}>}0) ,[Department Name]))

Hope it will help you out!

Vivek12
Contributor II
Contributor II
Author

Hey @AronC 

Thanks for help on this. I tried to refine it but it is still not serving the purpose. 
Can you please briefly explain me the working behind the expression so that I can see if the changes I am making to the expression are logical or not?

 

Sorry for my naivety on this, I am new to Qlik

AronC
Partner - Creator II
Partner - Creator II

The Aggr is creating a temporary table with the sum of value and department name. Then you sum the values from this temporary table. Withing the aggr when summing Value the problem is that A and B will disapear from youer table if you only sum the Parameter type =Z. There fore you add the sum 0 for Departments A and B. For the Table you provided this expression worked.