FIND THE WEEK

[marikabi]

Hello Guys,

I am trying to define a function in a table in order to calculate for each carrier (the only dimension in the table) what is the first week and the last week of traffic.

It means that in some cases, we started to use the carrier not from the beginning, but some time later. Viceversa, in some cases we stOpped to use a carrier some time before now.

I already use a function to calculate the traffic in the 1st week:

FirstSortedValue( aggr(sum([INBOUND TRAFFIC]),W_order, CARRIER), Aggr(W_order,W_order,CARRIER))

and in the last week:

FirstSortedValue( aggr(sum([INBOUND TRAFFIC]),W_order, CARRIER), Aggr(-W_order,W_order,CARRIER))

The field W_order  associates an ascending number to each week starting from week 1 (first week in 2016)...

Do you have any idea?

Many thanks in advance

[Digvijay_Singh]

How do you understand from data that carrier usage is started or ended?

Please share sample data and expected output, it will help to give suitable response.

[sunny_talwar]

Not sure if this makes any difference or not, but try this

FirstSortedValue(DISTINCT aggr(sum([INBOUND TRAFFIC]),W_order, CARRIER), -Aggr(W_order,W_order,CARRIER))

[marikabi]

Hello,

Your formula gives me back the same result..

But I am not looking for this:

the formula I wrote is to find the traffic volume; now I want to find the number of the week in which this traffic has been registered.

For example

Using the first formula I obtain as a result 1K minutes.

I need to find a way to represent that this 1K min was in week 5..

Hope now it's clear

Thank you

[marikabi]

I understand it because no traffic volume is registered... (INBOUND TRAFFIC)

If this sum(INBOUND TRAFFIC) is greater than 0 it means we are using that carrier..

So I know that the first week where the traffic volume was > 0 it's the first week we started to use that carrier...

Given the below data

1) using the first formula I wrote I obtain 4,795,237

2) using the second formula I wrote I obtain 6,831,990

Now I need the two formulas to obtain 9 and 15 (as weeks)

Thank you

WEEK	15	14	13	12	11	10	9
CARRIER	INBOUND TRAFFIC (min)	INBOUND TRAFFIC (min)	INBOUND TRAFFIC (min)	INBOUND TRAFFIC (min)	INBOUND TRAFFIC (min)	INBOUND TRAFFIC (min)	INBOUND TRAFFIC (min)
ABC	6,831,990	4,392,016	4,771,824	5,089,814	5,202,735	4,467,173	4,795,237

[sunny_talwar]

Wouldn't it be just Min and Max?

Max(W_order)

Min(W_order)

[marikabi]

I can't believe I haven't thought t use it

It was so easy!

Thank you so much

[marikabi]

Hi,

sorry for disturbing you again

I have also another field named "comment", where for each W_order there are some information...

I would like to add also this comment info, related both to the first week and also to the last week.

Is there a way to do it?

Let's say I used as a name for the two expressions: W1 and Ww..

Once again thank you

[sunny_talwar · Accepted solution]

May be like this

FirstSortedValue(Comment, Aggr(W_order,W_order,CARRIER))

FirstSortedValue(Comment, -Aggr(W_order,W_order,CARRIER))

[marikabi]

Amazing it works fine!!

Thank you again