Skip to main content
Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Search instead for
Did you mean:
Contributor

## 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)

• ### Set Analysis

3 Replies
Partner - Creator

@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!

Contributor
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

Partner - Creator

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.

Tags
Community Browser