Issue with a calculated dimension and a aggr function

[Anonymous]

Hello everyone!

I've been using a created dimension in order to organize countries code's (AllocTransfOrigMarketCd) in groups.The expression is this one:

=if(match(AllocTransfOrigMarketCd,'14','15','93'),'AU+CE+CH',

  if(match(AllocTransfOrigMarketCd,'22','23'),'BL+HL+NE',

        if(match(AllocTransfOrigMarketCd,'999','AC','AM','AP','OF','27','.','SET','30','PK','73','GPS','SP','SCO'),'Other',

            if(match(AllocTransfOrigMarketCd,'91','92','92Z','92L','92M','92O','91Z','91L','91M','91A','91O','92A'),'Overseas',

                if(match(AllocTransfOrigMarketCd,'17','28'),'UK+IR',

                    AllocTransfOrigMktDescrip)))))

I'll try to explain better what I want with this expression:

Let's say this dimension is called Office. If AllocTransfOrigMarketCd is not mentioned on any match function of Office definition, the name of the group of that field in the dimension Office should be its correspondent AllocTransfOrigMktDescrip.

For example, as AllocTransfOrigMarketCd= 10  is not mentioned, it should be include in a group called PORTUGAL because PORTUGAL is the AllocTransfOrigMktDescrip for  AllocTransfOrigMarketCd= 10.

This expression works pretty fine when I'm using a pivot table with simple measure.

The trouble  starts when I'm using and agregatte function as  a measure in a pivot table.

For example, I have this measure in a pivot table:

=if(getfieldselections(AllocTransfInsDate)>0,

count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId) / aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip),

count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId) / aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip))

Wich means I'm calculating relative weights by aggregating value by AllocTransfOrigMktDescrip.

If I use AllocTransfOrigMktDescrip as a row dimension, everything goes fine… but if I use the calculated dimension "Office", QS shuffles and output something like the image attached.I think that's because I aggregate the measure by AllocTransfOrigMktDescrip… Does anyone know what I can do better?

[sasiparupudi1]

It is better if you created a new field in your script using applymap

map_values:

Mapping Load

*

INLINE

[

AllocTransfOrigMarketCd,Result

14,AU+CE+CH

15,AU+CE+CH

93,AU+CE+CH

22,BL+HL+NE

23,'BL+HL+NE

.

.

.

];

YourTable1:

Load

your filed,

,AllocTransfOrigMarketCd,

AllocTransfOrigMktDescrip

Applymap('map_values',AllocTransfOrigMarketCd,AllocTransfOrigMktDescrip) As NewGroup

Resident YourTable;

In the front end, you could use NewGroup withouth having to write a complex expression.. hope this helps

Sasi

[Digvijay_Singh]

If you surround your Aggr with Sum, does it make any difference? -

=if(getfieldselections(AllocTransfInsDate)>0,

count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId) / Sum(aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip)),

count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId) / Sum(aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip)))

[Anonymous]

Thank you Sasidhar,

That would be a quick way to solve the problem but, unfortunately, I'm not allowed to change the script.

[Anonymous]

Thank you Digvijay! We are on the right way... The pivot table groups the fields as I wish, but the measure output is now incorrect... I'm working on this now! thank you!

[Anonymous]

Digvijay, just to update you:

in order to keep measure right we must add an DISTINCT after the sum functions you've add. Like this:

=if(getfieldselections(AllocTransfInsDate)>0,

  count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId) / sum(DISTINCT aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip)),

  count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId) / sum(DISTINCT aggr(NODISTINCT count({<AllocTransfTransfStat=-{"CANCEL"},AllocTransfInsDateYear={"$(=Year(Today()))"}, AllocTransfInsDateWeek={"$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip)))

thank you, your help was indespensible.