7 Replies Latest reply: Jul 28, 2016 11:16 AM by Christian Wylezol

count and or aggr different dimension lines on one dimension entry

Hi,

i have a tricky Count to do and hope someone of you has a good idea.

I have a Pivot-table with a structure (second column). The third column Shows the new adress and the forth column Shows the old adress.

My issue is:

The last column "pos" should count how much different "structure"-lines are used by this IFS-Konto entry "111110"

The result would be in every "pos"-line 4.

Does anybody of you can help me with that?

I have nearly 1300 entries in the column IFS_Konto and Need to Analyse this quick.

I thought of Count and aggr in combination but I dont get it to work.

Chris

• Re: count and or aggr different dimension lines on one dimension entry

Not sure what you expression is, but can try adding this to your expression:

TOTAL <IFS_Konto>

• Re: count and or aggr different dimension lines on one dimension entry

Hi Sunny,

until now I just tried some calculations but nothing worked, so I have nothing to Support your Suggestion.

I am also not sure if I understand you correcty, so please excuse my "again-questioning"

Would your calculation show me how many different structure-lines are used by this one "IFS_Konto"-Line with the entry "111110"?

Could you try to give me the complete formula? It would help me the most ;-)

Chris

• Re: count and or aggr different dimension lines on one dimension entry

I guess I am not sure what is pos counting here. Can you may be show me what is your expression for pos?

• Re: count and or aggr different dimension lines on one dimension entry

Hi Sunny,

the exact Expression from the Screenshot is not available anymore.

//Count(Aggr(CODA,IFS_Konto_ID))
//aggr(count(distinct {\$} CODA),IFS_Konto_ID)
//aggr(count(CODA), IFS_Konto_ID, CODA)
//aggr(count(CODA),CODA,IFS_Konto_ID)
//max(aggr(count(IFS_Konto_ID), CODA, IFS_Konto_ID))
//count(IFS_AccountPathsource)
//count({<IFS_Konto_ID = {"*"} >} distinct CODA)

Chris

• Re: count and or aggr different dimension lines on one dimension entry

Hi Sunny,

I tried a Little bit and with a filter on Dimension "IFS_Konto" it is shown correct.

(6 Accounts of one IFS_Konto in 5 different structure-lines). This is the Expression I used.

(count(total(Structure)))

Now I just have one issue. Even without a filter it should be shown like above (Without Filter: the amount is not at 5 anymore but 325).

Do you have an idea?

Chris

• Re: count and or aggr different dimension lines on one dimension entry

Count(TOTAL <Kontotyp> Structure)

• Re: count and or aggr different dimension lines on one dimension entry

Wow, Sunny,

thank you very much. it works

Chris