total traffic in the last week

[marikabi]

Hi all,

I am trying to calculate the total traffic volume in the last week for which I have reports.

I am using this formula:

sum({$<WEEK = {"$(=max(WEEK))" }> } [TRAFFIC])

What I need, is to also put a condition on the traffic volumes: I need the last week volume, where the total volume is >0.

Could you please help me?

Many thanks,

Marika

[Anil_Babu_Samineni]

First try as Static and then we can discuss, If values comes then we may think better opportunity

sum({$<WEEK = {'02'}, YEAR={'2017' } , [TRAFFIC] = {">0"} >} [TRAFFIC])  

or

If(Len(Sum([TRAFFIC]))>0,sum({$<WEEK = {'02'}, YEAR={'2017' } >} [TRAFFIC]))

[marikabi]

Using the condition WEEK=2 it does work, giving me the correct traffic value, using both the formulas...

[trdandamudi]

May be as below:

=sum({$<WEEK = {'$(=max(Week))'} > * <TrafficVolume= {'>0'}>} [TRAFFIC])

Note: Please make sure that the field names are correct because they are case sensitive

[marikabi]

hi,

it doesn't work...

[trdandamudi]

Can you please provide sample data with expected output..

[marikabi]

Ok,

this is an extract of a pivot, where I have for each country a traffic volume (note that in the data source for each country I have more cities).

For this "last week traffic" I am using the dimension WEEK (not WEEK_YEAR).

So I expect to obtain for 2016 : 7,993,584

and for 2017: 29,403,008

WEEK_YEAR	2_17	1_17	52_16	51_16	50_16	49_16
COUNTRY	TRAFFIC (min)	TRAFFIC (min)	TRAFFIC (min)	TRAFFIC (min)	TRAFFIC (min)	TRAFFIC (min)
A	8,725,814	6,926,585	3,592,148	6,146,049	8,554,312	8,336,841
B	4,846,770	2,978,479	805,038	3,139,271	4,465,576	4,411,841
C	8,506,241	6,498,715	1,745,381	5,733,842	8,817,456	8,707,397
D	4,980,485	2,350,338	1,012,816	3,053,196	4,438,554	4,447,862
E	2,343,698	1,675,338	838,201	1,447,619	1,969,252	1,925,210
total	29,403,008	20,429,455	7,993,584	19,519,977	28,245,150	27,829,151

[trdandamudi]

For some reason I am not able to follow your sample. I am attaching a simple sample file here can you take a look and let me know what is your desired output.

[Anonymous]

Try

=sum(if(Week=aggr(max({$<Traffic Volume={'>0'}>}Week),Year), TrafficVolume,0))

[Anil_Babu_Samineni · Accepted solution]

Okay, Now coming to real scenario try this

sum({$<WEEK = {'$(=Min(Month(AddMonths(Date(MakeDate(2017,02,01),'DD-MM-YYYY'),-1)))+1)'}, YEAR={'2017' } , [TRAFFIC] = {">0"} >} [TRAFFIC])

You can set as per you need, Here the problem is Max(Week) Give only 52nd Week. This case, You should create one flag for that and then try with using Flag

Load *,

          If(Min(Month(AddMonths(Date(DateField,'DD-MM-YYYY'),-1)))+1)='02', 1, 'Flag was incorrect') as Flag1 //DateField-- Your Field Name, I mean which Week, Year, Month Coming from. You should use that Field name to consider as a File

From Data Source;

Then, Try like below

sum({$<Flag1 = {'1'}, YEAR={'2017' } , [TRAFFIC] = {">0"} >} [TRAFFIC]) //This Will give Traffic values for 2017 and 2nd week where Is Not Null.

HTH