Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have one scenario, I have to compare data between non-associated data.
DataSet:
CODE | CATEGORY | Industry | InsCode | Salary |
AAA | Qlik Sense | Banking | FIN.01.005.E4A | 249996 |
AAA | Qlik Sense | Banking | FIN.01.005.E4A | 5000 |
AAA | QlikView | Banking | AK.02.001.P40 | 38950 |
ABC | QlikView | Banking | AK.02.001.P40 | 39975 |
ABC | QlikView | Banking | AK.02.001.P40 | 4000 |
ABC | QlikView | Banking | AK.02.001.P40 | 39975 |
BBB | SWIFT | Banking | AK.02.001.P40 | 38000 |
XYZ | Spot Fire | Banking | FIN.02.001.P60 | 56375 |
XYZ | Spot Fire | Banking | FIN.02.001.P60 | 1350 |
123 | Splunk | Banking | FIN.03.001.E30 | 1200 |
311 | BO | Banking | FIN.03.001.E20 | 4590 |
AAA | Qlik Sense | Banking | FIN.03.001.E13 | 148596 |
1056 | Tableau | Banking | TIN.03.002.E10 | 224052.5 |
ABC | QlikView | Banking | FIN.03.001.M50 | 56373 |
ABC | QlikView | Banking | FIN.03.001.M50 | 780 |
ABC | QlikView | Banking | FIN.03.001.M50 | 56375 |
1056 | Tableau | Banking | FIN.03.002.M5 | 82808 |
1056 | Tableau | Banking | FIN.03.002.M5 | 100000 |
1056 | Tableau | Banking | FIN.03.002.M5 | 113861 |
ABC | QlikView | Banking | FIN.03.002.M5 | 56375 |
POP | Micro Strategy | Sales | AK.02.001.P40 | 56375 |
POP | Micro Strategy | Sales | AK.02.001.P40 | 38950 |
POP | Micro Strategy | Sales | FIN.03.001.E13 | 39975 |
POP | Micro Strategy | Sales | FIN.02.001.P90 | 38000 |
MAN | Power BI | Sales | FIN.02.001.P60 | 56375 |
MAN | Power BI | Sales | FIN.03.001.E30 | 82808 |
MEN | Birt | Sales | FIN.03.001.E20 | 107000 |
MEN | Birt | Sales | FIN.03.001.E13 | 113861 |
NIN | Agile | Sales | TIN.03.002.E10 | 56375 |
NIN | Agile | Sales | FIN.03.001.M50 | 38000 |
NIN | Agile | Sales | FIN.03.001.M50 | 56375 |
I did group by Salary based on Industry and InsCode and applied back to main table using mapping table
Row Labels | Average of Salary |
Banking-AK.02.001.P40 | 32180 |
Banking-FIN.01.005.E4A | 127498 |
Banking-FIN.02.001.P60 | 28862.5 |
Banking-FIN.03.001.E13 | 148596 |
Banking-FIN.03.001.E20 | 4590 |
Banking-FIN.03.001.E30 | 1200 |
Banking-FIN.03.001.M50 | 37842.66667 |
Banking-FIN.03.002.M5 | 88261 |
Banking-TIN.03.002.E10 | 224052.5 |
Sales-AK.02.001.P40 | 47662.5 |
Sales-FIN.02.001.P60 | 56375 |
Sales-FIN.02.001.P90 | 38000 |
Sales-FIN.03.001.E13 | 76918 |
Sales-FIN.03.001.E20 | 107000 |
Sales-FIN.03.001.E30 | 82808 |
Sales-FIN.03.001.M50 | 47187.5 |
Sales-TIN.03.002.E10 | 56375 |
Grand Total | 64604.04839 |
Please find the attached spread sheet, I did exercise.
confusing with explanation, so attached spread sheet - hope it helps!
Thanks in Advance!
you may not want to determine the average in the script. because when the user filters on code for example, then the average is wrong as the average disregards the code. you can easily determine the average in the UI instead:
avg(salary) and you can get the comparison using Salary against the average (with Salary as a dimension)
Thanks for response!
Your right - average calculation is normal, but whatever user selected first set of selection - what we have data displayed and aggregated data, this data should be apply to second set of selections.
Created simple data set and attached spread sheet in detail..
I think hope it help below screen and attachment
Anyone did face such type of requirement!
Please I need suggestions.
try this:
expression you are looking for is
=avg(total <InsCode> Salary)
InsCode needs to be in the chart so it can be properly aggregated
im replying in reverse fashion
the understanding here is you want the average per InsCode regardless of Category and Industry and you want to compare per category and industry
also InsCode can be shared across Category and Industry looks like
Anyone can help me on this?
I'm expecting result as attached screen.
It's not working the way I expect it to