topic Re: FIND THE WEEK in QlikView

FIND THE WEEK

marikabi — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: FIND THE WEEK

Digvijay_Singh — Mon, 01 May 2017 16:15:57 GMT

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.

Re: FIND THE WEEK

sunny_talwar — Mon, 01 May 2017 16:18:53 GMT

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))

Re: FIND THE WEEK

marikabi — Mon, 01 May 2017 17:17:18 GMT

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

Re: FIND THE WEEK

marikabi — Mon, 01 May 2017 17:22:55 GMT

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

Re: FIND THE WEEK

sunny_talwar — Mon, 01 May 2017 17:25:57 GMT

Wouldn't it be just Min and Max?

Max(W_order)

Min(W_order)

Re: FIND THE WEEK

marikabi — Mon, 01 May 2017 17:31:35 GMT

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

It was so easy!

Thank you so much

Re: FIND THE WEEK

marikabi — Mon, 01 May 2017 17:48:17 GMT

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

Re: FIND THE WEEK

sunny_talwar — Mon, 01 May 2017 18:12:41 GMT

May be like this

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

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

Re: FIND THE WEEK

marikabi — Mon, 01 May 2017 18:15:44 GMT

Amazing it works fine!!

Thank you again