Solved: Re: total traffic in the last week - Qlik Community

marikabi · ‎2017-01-19

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 · ‎2017-01-20

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

MindaugasBacius · ‎2017-01-19

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

Anil_Babu_Samineni · ‎2017-01-19

You can try this too

If(Len(Sum([TRAFFIC]))>0,sum({$<WEEK = {"$(=max(WEEK))" }> } [TRAFFIC]))

Or

I assume, You must have Field called Traffic Volume

If(Len(Sum([TRAFFIC Volume]))>0,sum({$<WEEK = {"$(=max(WEEK))" }> } [TRAFFIC]))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

marikabi · ‎2017-01-19

Actually it is not working. For the 2017 I already have some info, like the traffic forecast or the holydays information till the week 52.

Using this code, it is still taking into account the week 52...

(In the formula there is also the condition on the year : YEAR={'2017'} )

Anonymous · ‎2017-01-19

Hi Marika,

You can add one more condition in your set analysis that is :- TraficVolume={">0"}.

Thanks,

Pooja

marikabi · ‎2017-01-19

Hi Anil,

it is still considering the week 52 of the 2017..

Any other idea?

marikabi · ‎2017-01-19

Only the first condition is satisfied, it is like an or condition. Is there any way to write an "and" condition?

marikabi · ‎2017-01-19

Hi,

I have already tried to add this condition with the comma.

But I am not obtaining the expected result..

Anil_Babu_Samineni · ‎2017-01-19

What you want to get the Data, I mean which week and year you want to show? Can you describe more

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

marikabi · ‎2017-01-19

Sure.

Adding the condition on the year these are the formulas I am using now:

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

or

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

So I expect to have the data about the 2nd week of 2017, because they are the last I have uploaded on the application.