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: 
thomastc
Contributor III
Contributor III

Force join on dummy group

Hello Group,

I hope someone can help.

I am having a problem where section access is reducing (some of) my data and the client does not want this. The area that nees to avoid being reduced is figures for a national bench mark.

Example, we are bench marking performance against the national figures for different practices.

We have a field called speciality. Say, as a for instance, practice A has no activity in speciality 100 then (when practice A is selected the national bench mark will not include any activity in Special 100).

I have constructed a load that successfully separates the bench mark figures from the main data using QUALIFY. So now the data is not reduced (for the national figures) but I also have no join between the bench mark speciality and the practice level speciality.

I have the Speciality activity and a BM.Speciality activity.

I have also separately bought in a dummy group of all possible specialties, let’s call this DSpec.

Is it possible to use DSpec as the dimension in a pivot table or graph and using some clever set analysis or if statements match the Speciality in one expression and the BM.Speciality in another to DSpec? If possible I want to avoid on click triggers and the like.

So in my chart I would like Dimension as DSepc  , expression 1 as the count of activity for Speciality and expression 2 as the count of activity for BM.Speciality.

I thought I could use something like (in the expression):

Expression 1 (Practice level)

=sum(if Speciality = DSpec, ActivityCount)

Expression 2 (National level)

=sum(if BM.Speciality = DSpec, BM.ActivityCount)

The above does not work.

Any help on this would be appreciated!

1 Solution

Accepted Solutions
thomastc
Contributor III
Contributor III
Author

I think I might have cracked it (for now): 

 

=

sum(if(BM.Spec_Code = SpecDumy, aggr(sum(BM.actSum), BM.Spec_Code

), 0))

If anyone has any other tips about this or has experienced anything similar around bench marking I would like to hear your comments.

View solution in original post

2 Replies
thomastc
Contributor III
Contributor III
Author

I think I might have cracked it (for now): 

 

=

sum(if(BM.Spec_Code = SpecDumy, aggr(sum(BM.actSum), BM.Spec_Code

), 0))

If anyone has any other tips about this or has experienced anything similar around bench marking I would like to hear your comments.

Not applicable

I didn't get ur section access thing. but i guess,

Dimention is  " SpecDumy "

Expression is

if(SpecDumy='Dspec',sum({<[BM.Spec_Code]={'Dspec'}>}Activity),if(SpecDumy='other Value',sum({<[BM.Spec_Code]={'Other Value'}>}Activity)))

It works if u hav less no of field values.