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: 
Anonymous
Not applicable

Issue with a calculated dimension and a aggr function

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?

5 Replies
sasiparupudi1
Master III
Master III

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
Not applicable
Author

Thank you Sasidhar,

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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.