Aggregate based on selection

[paulwalker]

Hi Community,

Can you please help me on below scenario..

I'm using comparison using states, If the user selects StateA then its filter data should be applied to StateB based on the key

I have few selections to generate dashboard like Industry, SubIndustry, Location etc...............

Suppose If user select Industry, it should be aggregation on Industry 

If user select Location, should be aggegation on Location

If user select both Industry and Location, should be aggregation on both (Industry and Location)

I have written code script level, I know this is not right approach  (If I go with below one have to write many combinations)

Industry:
LOAD * where List1>=3;
LOAD SURVEY, MUPC_CODE,Industry,
AVG(BASE_SALARY) as AvgBS_Industry,
COUNT(DISTINCT OrgCode) as List1
FROM [lib://Test/Jordan.qvd](qvd)
Group by SURVEY, MUPC_CODE, Industry;

IndustryLookup:
MAPPING LOAD SURVEY&MUPC_CODE&Industry as Key,
AvgBS_Industry
Resident Industry;
Drop Table Industry;
//=========================================
SubIndustry:
LOAD * where List1>=3;
LOAD SURVEY, MUPC_CODE,Location,
AVG(BASE_SALARY) as AvgBS_Location,
COUNT(DISTINCT OrgCode) as List1
FROM [lib://Test/Jordan.qvd](qvd)
Group by SURVEY, MUPC_CODE, Location;

SubIndustryLookup:
MAPPING LOAD SURVEY&MUPC_CODE&Location as Key,
AvgBS_Location
Resident SubIndustry;
Drop Table SubIndustry;

Expression: using expression like below, each selection I'm doing aggregation backend script and using expression selection wise.... 

IF(GetSelectedCount(Industry, False(), 'Group1'),
AVG(AGGR((AVG({Group2<MUPC_CODE=P(Group1::MUPC_CODE), SURVEY=$(vSurvey)>} BASE_SALARY) - AVG(AGGR(NODISTINCT AVG({Group1<MUPC_CODE=P(Group2::MUPC_CODE), SURVEY=$(vSurvey)>} avgIndustry), MUPC_CODE)))
/AVG(AGGR(NODISTINCT AVG({Group1<MUPC_CODE=P(Group2::MUPC_CODE), SURVEY=$(vSurvey)>} avgIndustry), MUPC_CODE)), [%Key])),

IF(GetSelectedCount(Location, False(), 'Group1'),
AVG(AGGR((AVG({Group2<MUPC_CODE=P(Group1::MUPC_CODE), SURVEY=$(vSurvey)>} BASE_SALARY) - AVG(AGGR(NODISTINCT AVG({Group1<MUPC_CODE=P(Group2::MUPC_CODE), SURVEY=$(vSurvey)>} avgLocation), MUPC_CODE)))
/AVG(AGGR(NODISTINCT AVG({Group1<MUPC_CODE=P(Group2::MUPC_CODE), SURVEY=$(vSurvey)>} avgLocation), MUPC_CODE)), [%Key]))))

what I have done working fine, but that is not correct approach - because if i have more selections have to write all the combination aggregations.

instead of these many aggregations, can we write in set analysis and aggregation should be auto change when user selects options?

Thanks in Advance!

[paulwalker]

Hi @Vegar,

can you please help me on this.. you gave one solution for this earlier 

https://community.qlik.com/t5/New-to-Qlik-Sense/Issue-with-expression/m-p/1783668#M179638

[Vegar]

I am not sure if I understand your description and sample completely, here is an suggestion.

What if you try to figure out which dimensions you do have a selection int and list them in an variable? Lets call the variable vDimensionsToAggregate. 

• If you make a seletion to Location then vDimensionsToAggregate will be: Location.
• If you make a seletion to Industry then vDimensionsToAggregate will be: Industry.
• If you make a seletion to Location and to Industry then vDimensionsToAggregate will be: Location, Industry

When you created that variable you can use it in your expression like this:

=aggr(avg(BASE_SALARY) ,$(=vDimensionsToAggregate))

I've tried to create this variable,vDimensionsToAggregate,  with the expression blelow. It should work, but it wont refresh properly on my desktop client. Feel free to try it on a server environment to see if it works better in such an environment. 

=replace(
trim(
if(getselectedcount(Location, true(), 'Group2')>0, ' Location')
& if(getselectedcount(Industry, true(), 'Group2')>0, ' Industry')
),
' ', ',')

[krishna20]

@paulwalker ,

Please follow below simple steps to achieve this.

Create an Inline table with all your dimensions you have.

Dynamic_Dim:

Load * Inline [

Dimshow,Dimsort

Industry,1

Location,2

];

Go to variable overview and create a variable vDimshow ,then under the definition 

Pick(Match(Dimsort, 1, 2),
'[Industry]',
'[Location]')

Final Expression:

AVG(AGGR((AVG({Group2<MUPC_CODE=P(Group1::MUPC_CODE), SURVEY=$(vSurvey)>} BASE_SALARY) - AVG(AGGR(NODISTINCT AVG({Group1<MUPC_CODE=P(Group2::MUPC_CODE), SURVEY=$(vSurvey)>} $(vDimshow)), MUPC_CODE)))
/AVG(AGGR(NODISTINCT AVG({Group1<MUPC_CODE=P(Group2::MUPC_CODE), SURVEY=$(vSurvey)>} $(vDimshow)), MUPC_CODE)), [%Key])

No if conditions needed, automatically variable passes the dimension into the set analysis whatever been selected.

Note: You need to display all your dimensions (Dimshow) from inline table, it will works based on the selections and enable always selected one value in listbox.

Regards

Krishna

[paulwalker]

Thanks for your response @krishna20 @Vegar 

can we write in set expression without aggregation like below script ....

I did try, but not able to achieve, can you please help me..... value should be 0.1%

I know this not correct way to achieve many aggregations each combination - can you please help

Industry:
LOAD * where List1>=3;
LOAD SURVEY, MUPC_CODE,Industry,
AVG(BASE_SALARY) as AvgBS_Industry,
COUNT(DISTINCT OrgCode) as List1
FROM [lib://Test/Jordan.qvd](qvd)
Group by SURVEY, MUPC_CODE, Industry;

//=========================================
SubIndustry:
LOAD * where List1>=3;
LOAD SURVEY, MUPC_CODE,Location,
AVG(BASE_SALARY) as AvgBS_Location,
COUNT(DISTINCT OrgCode) as List1
FROM [lib://Test/Jordan.qvd](qvd)
Group by SURVEY, MUPC_CODE, Location;

[rwunderlich]

You seem to be changing your measure depending on selection. 

Industry: Avg(...avgIndustry)

Location: Avg(...avgLocation)

What would the measure be if user selected both Industry and Location?

It may be helpful if you explained what your overall business objective is.  It may be that we can suggest an easier path.

-Rob

[paulwalker]

I have created combination Industry and Location aggregation.

like below:  I know this is the wrong approach...

LOAD * where List1>=3;
LOAD SURVEY, MUPC_CODE,Industry, Location,
AVG(BASE_SALARY) as AvgBS_IndLoc,
COUNT(DISTINCT OrgCode) as List1
FROM [lib://Test/Jordan.qvd](qvd)
Group by SURVEY, MUPC_CODE, Industry, Location;

Please give suggestion, how to implement simpler way...

Business: Measeure is BASE_SALARY
User have 6 selections (Industry, SubIndustry, Locaton, HQLocation, Revenue, HeadCount),
when user select any option that should be aggregate on dimension-
if user select multiple selection should be based on both dimension.

[rwunderlich]

I understand you want to always aggregate BASE_SALARY by SURVEY, MUPC_CODE. If any values are selected in other key fields, you want to include those field names in the aggr dims as well. 

1. Create a variable "vDimList" with content below. Be sure to include the leading "=" in the definition.  I've added two fields, extend it with the rest of your fields. 

='SURVEY, MUPC_CODE'
& IF(GetSelectedCount(Industry, False(), 'Group1'), ',Industry', '')
& IF(GetSelectedCount(Location, False(), 'Group1'), ',Location', '')

2. Your measure should then be written like:

Avg(Aggr(Avg(BASE_SALARY), $(vDimList)))

-Rob