Force join on dummy group

thomastc — Tue, 12 Jun 2012 10:38:20 GMT

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!

Re: Force join on dummy group

thomastc — Tue, 12 Jun 2012 11:02:17 GMT

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.

Re: Force join on dummy group

Tue, 12 Jun 2012 11:37:06 GMT

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.

topic Force join on dummy group in QlikView

Force join on dummy group

Re: Force join on dummy group

Re: Force join on dummy group