Is this even possible?

[johanfo2]

I really don't know how to search the forums for this. I believe what I try to do here is beyond the simple query. 

Lets say you have a fact table:

Sender     Receiver

----------------------------------

A              B

A              B

A              C

B              A

C              A

C              A

(etc)

Now, I want to display how many times A sends to B.  This is simple:

Create a 2 dim table, and add a expression: count(Sender)

This wil give you something like this:

Sender Receiver Count

---------------------------------

A         B            2

A         C            1

B         A            1

C         A            2

Now, here is the real question!  Is it even possible to add a 4th column, which shows the reverse. That is how many time the receiver have sendt to the sender?  What I want is this:

Sender Receiver Count   CountReverse

----------------------------------------------

A         B            2          1

A         C            1          2

B         A            1          2

C         A            2          1

But unfortunately, I haven't even been close to find an expression that creates this 4th colum?

Any ideas are greatly appreciated.

JF

[thomas_skariah]

Hi Johan,

Try the attached application.

Hope it will work for you.

Regards,

Tom

[johanfo2]

Tom,

If I understand your attached sample correctly, you just create two different tables, where you change the dimentions and do a regular count.  Further, I didn't see the purpose of doing the concatenation of sender and receiver in the example.  Am I missing something?

My problem is that I would like to have both "counter columns" in the same table. 

[#4]

Hi,

I did as you specified, please check the attached file.

Thanks,

Niranjan M

[johanfo2]

This was helpful, and will enable me to solve my problem.  However, optimally, I would like a solution to this that doesn't require load script logic and extra tables.  Maybe am asking to too much ?

[johanfo2]

Based on previous suggestions, I solved it the following way:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='₹ #,##0.00;₹ -#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Data:

Load

          Sender As Sendero,

          Receiver As Receivero,

          Sender & '_' & Receiver As DirectRelation,

          Receiver & '_' & Sender As BackRelation,

          Id

;

LOAD * INLINE [

    Sender, Receiver, Id

    A, B, 1

    A, B, 2

    A, C, 3

    B, A, 4

    C, A, 5

    C, A, 6

    A, B, 7

    A, D, 8

];

Temp:

LOAD

          DirectRelation as DirectRelation,

          count(DirectRelation) As dcount

Resident Data

group by DirectRelation;

Concatenate (Temp)

LOAD

          BackRelation as DirectRelation,

          count(BackRelation) As bcount

Resident Data

group by BackRelation;

Further, I created a chart with 2 dimensions (sender , receiver) and two expressions 1) sum(dcount) 2) sum(bcount)

[johanfo2]

This is an even better solution, so I posted it aswell (see below).  However, now I have encountered a new problem: 

-  If I in addion to the Sender, Receiver fields have a "Year" field, and when I select a year, I only want to count the backrelations in that year.  Is this possible?  It seems like my solution below is very static in the sense that i will not allow such a selection.

Data:

Load

          Sender As Sendero,

          Receiver As Receivero,

          Sender & '_' & Receiver As DirectRelation,

          Receiver & '_' & Sender As BackRelation,

          Id

;

LOAD * INLINE [

    Sender, Receiver, Id

    A, B, 1

    A, B, 2

    A, C, 3

    B, A, 4

    C, A, 5

    C, A, 6

    A, B, 7

    A, D, 8

];

left join (Data)

LOAD

          BackRelation as DirectRelation,

          count(BackRelation) as BackCount

Resident Data

Group by BackRelation;

[johanfo2 · Accepted solution]

I'm posting my final solution!  I had to work quite a bit, but this was how I wanted it to be. 

In this example, the default is to count all the backrelations accross all dates.  If you select date(s), it will only count the backrelations within those dates.

The key was to use two tables, and this expression: =count({<Date2=P(Date)>}Date2)

Arriving at the solution was a mess, and I'm not even completely confident of how everyting works 😕 , yet it does.  If anybody knows an even simpler way, please let me know.

Thanks to all who contributed with ideas and samples.