counter value within last 7 days

[amien]

i have this table:

key, unique address, date, #counter

1, 100, 20160101, 1

2, 100, 20160102, 1

3, 100, 20160502, 1

i need this result in a straight table

dim_address, dim_date, expression

100, 20160101, 1

100, 20160102, 2  <---

100, 20160502, 1

<-- this record does a sum(#counter) but looks for the last 7 days. It therefor has a hit on 20160101 and shows 2.

What expression do i need for this? Point in time is not working in this case.

Thanks in advanced

[kaushiknsolanki]

Hi,

It would be great if you share some sample data.

Regards,

Kaushik Solanki

[amien]

the sample data is in the first threat

sum(#counter) should give me the current one of the dimension + i need to sum(#counter) of the previous 7 days (if any), within the unique address

[sunny_talwar]

I think what kaushik.solanki‌ is looking for a more comprehensive example where is would be easier to see the logic. Would you be able to provide few more data points so that the logic becomes clearer. Also do you want this in the front end, or a script based solution can also work?

[amien]

Fair Enough;

I uploaded an example (starting post). On the red line, a need the sum of the blue line in an expression. the blue line looks 7 days back within the same Dim (Street1). Front end solution is preferred.

[sunny_talwar]

Are you looking for one of these solutions?

Expressions

1) Sum(Value)

2) RangeSum(Above(Sum(Value), 0, 7))

3) If(WeekDay(Date) = 'Fri', RangeSum(Above(Sum(Value), 0, 7)), Sum(Value))

[effinty2112]

Hi Amien,

                    Copy your expression and make it accumulate 7 steps back. The accumulation will reset when the value of Dim changes:

Regards

Andrew

[amien]

Yes and now

it will work only if all days have a record, which will not be the same. For specific days the fact might be missing.

Plus the 7 trick will only work on the current selection right? so if a user will select 2016, it will leave out the 31-12-2015 record right?

[sunny_talwar · Accepted solution]

Which expression is moving in the right direction?

To solve your concerns for the 1st problem, you can use The As-Of Table‌ and to address second issue just in the expressions you can try something like this out:

2) RangeSum(Above(Sum({<Year, Month, Date>}Value), 0, 7)) * Avg(1)

3) If(WeekDay(Date) = 'Fri', RangeSum(Above(Sum({<Year, Month, Date>}Value), 0, 7)), Sum({<Year, Month, Date>}Value)) * Avg(1)

But I think based on what you are looking for, I would suggest using The As-Of Table approach because this will address both your issues at once.

[sunny_talwar]

Added the As-Of Table approach for you to play around with

Script:

t1:

LOAD

  %Datum_key,

  'Street 1' As Dim,

  Num(RAND()*10,'0') As Value

RESIDENT Calender;

Concatenate(t1)

LOAD

  %Datum_key,

  'Street 2' As Dim,

  Num(RAND()*10,'0') As Value

RESIDENT Calender;

AsOfTable:

LOAD Date(%Datum_key) as NewDim,

  %Datum_key - IterNo() + 1 as %Datum_key

Resident t1

While IterNo() <= 7;