Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
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.