Re: Calculating value on same hour/same weekday fo... - Qlik Community

Anonymous · ‎2018-04-09

I am trying to find out the value of a field as it was on the same time, same weekday for the last 3 weeks.

For example:

CurrentTime = 04/30/17 5:00:00 PM

I want average of a measure for the following datetime: 04/23/17 5:00:00 PM

04/16/17 5:00:00 PM

04/09/17 5:00:00 PM

Is there a way to do it?

petter · ‎2018-04-10

To get the same time as a week ago - which would of course also be the same weekday your can simply subtract 7 from the datetime field:

aDateField-7

The resulting value will not be formatted so you might want to do the formatting:

Date(aDateField-7,'M/D/YY hh:mm:ss tt')

petter · ‎2018-04-10

The test data I used:

SET ThousandSep=',';
SET DecimalSep='.';
SET DateFormat='M/D/YY';
SET TimeFormat='hh:mm:ss tt';
SET TimestampFormat='M/D/YY hh:mm:ss tt';


SOMEDATA:
LOAD
  RowNo() AS R#,
  Today(1)+1-Floor(RowNo()/10)/24 AS #Date,
  Date(Today(1)+1-Floor(RowNo()/10)/24,'M/D/YY hh:mm:ss tt') AS Date,
  Ceil( Rand()*10000) AS M
AUTOGENERATE
  24*21*8   // This will be the RowNo() - the number of hours backwards in time from Today(1)
WHILE
  IterNo()<=10;   // generate 10 values per hour


SELECTION_DATES_DATA_ISLAND:
LOAD 
  Today(1)+1-Floor(RowNo()/10)/24 AS #D,
  Date(Today(1)+1-Floor(RowNo()/10)/24,'M/D/YY hh:mm:ss tt') AS D
AUTOGENERATE
  24*21*8;   // This will be the RowNo() - the number of hours backwards in time from Today(1)

Created a data island at line 18 to enable selection of a CurrentDateTime.

The two tables show two approaches to filter out the three dates. The first is by using a set expression to pick them in the measure. The second is by using an If() with the dimension to pick out the relevant days there. The user needs to select one and only one datetime on the filter pane to the left. You can add a Calculating Condition to the table to tell the user that they need to select only one value:

Anonymous · ‎2018-04-10

Thank you for your reply.

My date column is 'PeriodStart'. And my measure is captured on an hourly basis.

Is there a way to achieve the same w/o creating a data island? I am not using any filter and just want the average for the most recent date i.e. just one value for the most recent date.

PeriodStart | Sales

03/30/2018 03:00:00 120

03/30/2018 02:00:00 115

.

03/23/2018 03:00:00 110

.

03/16/2018 03:00:00 100

.

03/09/2018 03:00:00 90

I want a calculation(measure) to show as a KPI that gives something like (120 - (110+100+90)/3). petter-s‌

Can I do that by adding a column expression/calculated field?

Anonymous · ‎2018-04-10

Data model is already built and loaded in the app. I don't have access to make changes to the data model. So, I wanted a calculated field/expression to find the above value.

petter · ‎2018-04-10

The expression you can put in your KPI is:

Sum({<PeriodStart={

'$(=Date(Max(PeriodStart) ,'M/D/YYYY hh:mm:ss tt'))'

}>} Sales)

-

Sum({<PeriodStart={

'$(=Date(Max(PeriodStart)- 7,'M/D/YYYY hh:mm:ss tt'))' ,

'$(=Date(Max(PeriodStart)-14,'M/D/YYYY hh:mm:ss tt'))' ,

'$(=Date(Max(PeriodStart)-21,'M/D/YYYY hh:mm:ss tt'))'

}>} Sales)/3

Dates is a pain in set expressions unless you work with pure numeric dates without formatting....

If you create a variable:

vMax0 =Date(Max(PeriodStart),'M/D/YYYY hh:mm:ss tt')

vMax7 =Date(Max(PeriodStart)-7,'M/D/YYYY hh:mm:ss tt')

vMax14 =Date(Max(PeriodStart)-14,'M/D/YYYY hh:mm:ss tt')

vMax21 =Date(Max(PeriodStart)-21,'M/D/YYYY hh:mm:ss tt')

Then the expression can be:

Sum( {<PeriodStart={'$(vMax0)'}>} Sales)-Sum( {<PeriodStart={'$(vMax7)','$(vMax14)','$(vMax21)'}>} Sales)/3

Anonymous · ‎2018-04-10

It does not work. Value in KPI is being displayed as '-'.

PeriodStart is added as a master dimension in the data model.

petter · ‎2018-04-11

It does work with the simple data model you described earlier but not with your real world data model as you experienced. So you will have to either adapt it to suit a more complicated/sophisticated data model or you will have to provide some test data that reflects it or a more comprehensive description of you real data model.

IMPORTANT point:

As I said in the previous post "Dates is a pain in set expressions unless you work with pure numeric dates without formatting...." which means that if the formatting (display of date and time) is inconsistent the logic in a set expression will break. That is why many prefer to have an extra field in a data model or in date dimension that is a pure numeric representation of a date so the set expression logic gets more robust and manageable.

Num(Today(1))=43201 but Today(1)=4/11/18 or 4/11/2018 or 04.11.2018 or ..... xxxx ..... (totally dependent on your country settings for date).

So it is safe to operate with pure numerical dates. However for display purposes we need the formatting to be able to recognized them as real dates.

As an example for the expression I suggested it could look like this - if you have a field named nPeriodstart that is just the number part of the date:

Sum({<nPeriodStart={'$(=Max(nPeriodStart)'}>} Sales)

-

Sum({<nPeriodStart={'$(=Max(nPeriodStart)- 7)' ,'$(=Max(nPeriodStart)-14)' ,'$(=Max(nPeriodStart)-21)' }>} Sales)/3

Anonymous · ‎2018-04-11

PeriodStartTime Sum(setup_defbear)

4/8/2018 8:00:00 AM 9246658

4/8/2018 7:00:00 AM 6346428

4/8/2018 6:00:00 AM 46473408

4/8/2018 5:00:00 AM 84585658

4/8/2018 4:00:00 AM 15073569

4/8/2018 3:00:00 AM 46090315

4/8/2018 2:00:00 AM 95139995

4/8/2018 1:00:00 AM 59363924

4/8/2018 12:00:00 AM 61808648

4/7/2018 11:00:00 PM 55328041

4/7/2018 10:00:00 PM 52921054

4/7/2018 9:00:00 PM -561720723

4/7/2018 8:00:00 PM 26808781

4/7/2018 7:00:00 PM 26816895

4/7/2018 6:00:00 PM 99666878

4/7/2018 5:00:00 PM 26388497

4/7/2018 4:00:00 PM 97583937

4/7/2018 3:00:00 PM 22231741

4/7/2018 2:00:00 PM 91700755

4/7/2018 1:00:00 PM 50824236

4/7/2018 12:00:00 PM 46220674

4/7/2018 11:00:00 AM 43097937

4/7/2018 10:00:00 AM 41117008

4/7/2018 9:00:00 AM -1697760

4/7/2018 8:00:00 AM 74117297

4/7/2018 7:00:00 AM 79503459

4/7/2018 6:00:00 AM 46185277

4/7/2018 5:00:00 AM 49034462

4/7/2018 4:00:00 AM 17493825

4/7/2018 3:00:00 AM -410364099

4/7/2018 2:00:00 AM 55079998

4/7/2018 1:00:00 AM 62248754

4/7/2018 12:00:00 AM 59045695

4/6/2018 11:00:00 PM 57134765

4/6/2018 10:00:00 PM 101302647

4/6/2018 9:00:00 PM 25844689

4/6/2018 8:00:00 PM 58380486

4/6/2018 7:00:00 PM 67233770

4/6/2018 6:00:00 PM 58825256

4/6/2018 5:00:00 PM 66079696

4/6/2018 4:00:00 PM 56714663

4/6/2018 3:00:00 PM 28222001

4/6/2018 2:00:00 PM 58128493

4/6/2018 1:00:00 PM 54691299

4/6/2018 12:00:00 PM 53825829

4/6/2018 11:00:00 AM 49041193

4/6/2018 10:00:00 AM 44584139

4/6/2018 9:00:00 AM 76168628

4/6/2018 8:00:00 AM 41731495

4/6/2018 7:00:00 AM 43133616

4/6/2018 6:00:00 AM 45607127

4/6/2018 5:00:00 AM 82764995

4/6/2018 4:00:00 AM 49291514

4/6/2018 3:00:00 AM 55802751

4/6/2018 2:00:00 AM 56611912

4/6/2018 1:00:00 AM 61453010

4/6/2018 12:00:00 AM 95479392

4/5/2018 11:00:00 PM 55843972

4/5/2018 10:00:00 PM 57448735

4/5/2018 9:00:00 PM 66293916

4/5/2018 8:00:00 PM 27945686

4/5/2018 7:00:00 PM 99303599

4/5/2018 6:00:00 PM 54108204

4/5/2018 5:00:00 PM 64926084

4/5/2018 4:00:00 PM 30743695

4/5/2018 3:00:00 PM 58666924

4/5/2018 2:00:00 PM 57796261

4/5/2018 1:00:00 PM 24781103

4/5/2018 12:00:00 PM 54102089

4/5/2018 11:00:00 AM 82646729

4/5/2018 10:00:00 AM 44635469

4/5/2018 9:00:00 AM 8698045

4/5/2018 8:00:00 AM 75512616

4/5/2018 7:00:00 AM -379029325

4/5/2018 6:00:00 AM 12673420

4/5/2018 5:00:00 AM 78357254

4/5/2018 4:00:00 AM 81173329

4/5/2018 3:00:00 AM 55687326

4/5/2018 2:00:00 AM 59648148

4/5/2018 1:00:00 AM 62183962

4/5/2018 12:00:00 AM 63605479

4/4/2018 11:00:00 PM 56391060

4/4/2018 10:00:00 PM 60674072

4/4/2018 9:00:00 PM 66715543

4/4/2018 8:00:00 PM 24228690

4/4/2018 7:00:00 PM 56209578

4/4/2018 6:00:00 PM 66512232

4/4/2018 5:00:00 PM 86989355

4/4/2018 4:00:00 PM 97279091

4/4/2018 3:00:00 PM 89222925

4/4/2018 2:00:00 PM 60301160

4/4/2018 1:00:00 PM 57113225

4/4/2018 12:00:00 PM 53915525

4/4/2018 11:00:00 AM 49665022

4/4/2018 10:00:00 AM 76539902

4/4/2018 9:00:00 AM 9460710

4/4/2018 8:00:00 AM 9532624

4/4/2018 7:00:00 AM 43023822

4/4/2018 6:00:00 AM 39680429

4/4/2018 5:00:00 AM 47181813

4/4/2018 4:00:00 AM 50892439

4/4/2018 3:00:00 AM 54327484

4/4/2018 2:00:00 AM 55337235

4/4/2018 1:00:00 AM 61030180

4/4/2018 12:00:00 AM 56792329

4/3/2018 11:00:00 PM 60712353

4/3/2018 10:00:00 PM 50761220

4/3/2018 9:00:00 PM 65959840

4/3/2018 8:00:00 PM 66418451

4/3/2018 7:00:00 PM 58627575

4/3/2018 6:00:00 PM 64765713

4/3/2018 5:00:00 PM 64221932

4/3/2018 4:00:00 PM 61434081

4/3/2018 3:00:00 PM 61842229

4/3/2018 2:00:00 PM 38855792

4/3/2018 1:00:00 PM 57232550

4/3/2018 12:00:00 PM 54918465

4/3/2018 11:00:00 AM 49015645

4/3/2018 10:00:00 AM 44275859

4/3/2018 9:00:00 AM -777278730

4/3/2018 8:00:00 AM 40858434

4/3/2018 7:00:00 AM 42419964

4/3/2018 6:00:00 AM 44749380

4/3/2018 5:00:00 AM 47538132

4/3/2018 4:00:00 AM 49697705

4/3/2018 3:00:00 AM 54946016

4/3/2018 2:00:00 AM 59249099

4/3/2018 1:00:00 AM 61121424

4/3/2018 12:00:00 AM 58941105

4/2/2018 11:00:00 PM 62997058

4/2/2018 10:00:00 PM 62010569

4/2/2018 9:00:00 PM 66020945

4/2/2018 8:00:00 PM 57795993

4/2/2018 7:00:00 PM 62355139

4/2/2018 6:00:00 PM 64737292

4/2/2018 5:00:00 PM 63349703

4/2/2018 4:00:00 PM 58090279

4/2/2018 3:00:00 PM 59763305

4/2/2018 2:00:00 PM 57861760

4/2/2018 1:00:00 PM 54594134

4/2/2018 12:00:00 PM 50777447

4/2/2018 11:00:00 AM 39995003

4/2/2018 10:00:00 AM 42951711

4/2/2018 9:00:00 AM 40631926

4/2/2018 8:00:00 AM -1163847173

4/2/2018 7:00:00 AM 41866364

4/2/2018 6:00:00 AM -1786226309

4/2/2018 5:00:00 AM 45000366

4/2/2018 4:00:00 AM 49523967

4/2/2018 3:00:00 AM 53786810

4/2/2018 2:00:00 AM 56452643

4/2/2018 1:00:00 AM 58800206

4/2/2018 12:00:00 AM 59959459

4/1/2018 11:00:00 PM 52667044

4/1/2018 10:00:00 PM 60493937

4/1/2018 9:00:00 PM 60791173

4/1/2018 8:00:00 PM 55039159

4/1/2018 7:00:00 PM 62349914

4/1/2018 6:00:00 PM -341536648

4/1/2018 5:00:00 PM 61443501

4/1/2018 4:00:00 PM 59490538

4/1/2018 3:00:00 PM 56294555

4/1/2018 2:00:00 PM 52184074

4/1/2018 1:00:00 PM 45736212

4/1/2018 12:00:00 PM 43929351

4/1/2018 11:00:00 AM 39632774

4/1/2018 10:00:00 AM 38828282

4/1/2018 9:00:00 AM 38867979

4/1/2018 8:00:00 AM 40328045

4/1/2018 7:00:00 AM 39369809

4/1/2018 6:00:00 AM 45159184

4/1/2018 5:00:00 AM 47936973

4/1/2018 4:00:00 AM 51864236

4/1/2018 3:00:00 AM 52999697

4/1/2018 2:00:00 AM 55157567

4/1/2018 1:00:00 AM -426852833

4/1/2018 12:00:00 AM 60302550

3/31/2018 11:00:00 PM 60063517

3/31/2018 10:00:00 PM 59606164

3/31/2018 9:00:00 PM 60648637

3/31/2018 8:00:00 PM 61944344

3/31/2018 7:00:00 PM 62165317

3/31/2018 6:00:00 PM 59938217

3/31/2018 5:00:00 PM 61050474

3/31/2018 4:00:00 PM 29825226

3/31/2018 3:00:00 PM 48708246

3/31/2018 2:00:00 PM 53706759

3/31/2018 1:00:00 PM 49560042

3/31/2018 12:00:00 PM 45014870

3/31/2018 11:00:00 AM 41024171

3/31/2018 10:00:00 AM 39724612

3/31/2018 9:00:00 AM 40047457

3/31/2018 8:00:00 AM 40786807

3/31/2018 7:00:00 AM 33748666

3/31/2018 6:00:00 AM 45452105

3/31/2018 5:00:00 AM 48519328

3/31/2018 4:00:00 AM 52406713

3/31/2018 3:00:00 AM 51045451

3/31/2018 2:00:00 AM 57952305

3/31/2018 1:00:00 AM 60713199

3/31/2018 12:00:00 AM 62202956

3/30/2018 11:00:00 PM 62057044

3/30/2018 10:00:00 PM 63381148

3/30/2018 9:00:00 PM 64015788

3/30/2018 8:00:00 PM 64990352

3/30/2018 7:00:00 PM 52415342

3/30/2018 6:00:00 PM 56471019

3/30/2018 5:00:00 PM 64197190

3/30/2018 4:00:00 PM 61262130

3/30/2018 3:00:00 PM 54906330

3/30/2018 2:00:00 PM 55442803

3/30/2018 1:00:00 PM 54425006

3/30/2018 12:00:00 PM 50467794

3/30/2018 11:00:00 AM 46173781

3/30/2018 10:00:00 AM 43054924

3/30/2018 9:00:00 AM -104713619

3/30/2018 8:00:00 AM 41339891

3/30/2018 7:00:00 AM -779772620

3/30/2018 6:00:00 AM 44825129

3/30/2018 5:00:00 AM -378507720

3/30/2018 4:00:00 AM 50646361

3/30/2018 3:00:00 AM 42625947

3/30/2018 2:00:00 AM 58608598

3/30/2018 1:00:00 AM 60880693

3/30/2018 12:00:00 AM 62803417

3/29/2018 11:00:00 PM 62542399

3/29/2018 10:00:00 PM 58819249

3/29/2018 9:00:00 PM 52259748

3/29/2018 8:00:00 PM 66104523

3/29/2018 7:00:00 PM 61636276

3/29/2018 6:00:00 PM 58936066

3/29/2018 5:00:00 PM -482686744

3/29/2018 4:00:00 PM 11688739

3/29/2018 3:00:00 PM 34369315

3/29/2018 2:00:00 PM 276452947

3/29/2018 1:00:00 PM 433375482

3/29/2018 12:00:00 PM 53296446

3/29/2018 11:00:00 AM 313950893

3/29/2018 10:00:00 AM 239556859

3/29/2018 9:00:00 AM 588764915

3/29/2018 8:00:00 AM -258044107

3/29/2018 7:00:00 AM -537107111

3/29/2018 6:00:00 AM 530924495

3/29/2018 5:00:00 AM -536244863

3/29/2018 4:00:00 AM 37349228

3/29/2018 3:00:00 AM 14775018

3/29/2018 2:00:00 AM -312640303

3/29/2018 1:00:00 AM 66252256

3/29/2018 12:00:00 AM 15056031

3/28/2018 11:00:00 PM 53572006

3/28/2018 10:00:00 PM 561032230

3/28/2018 9:00:00 PM -493057719

3/28/2018 8:00:00 PM 228524922

3/28/2018 7:00:00 PM -1019777138

3/28/2018 6:00:00 PM 10218052

3/28/2018 5:00:00 PM 7454295

3/28/2018 4:00:00 PM 3640775

3/28/2018 3:00:00 PM 0

3/28/2018 2:00:00 PM 0

3/28/2018 1:00:00 PM 0

3/28/2018 12:00:00 PM 0

3/28/2018 11:00:00 AM 0

3/28/2018 10:00:00 AM 0

3/28/2018 9:00:00 AM 0

3/28/2018 8:00:00 AM 0

3/28/2018 7:00:00 AM 0

3/28/2018 6:00:00 AM 0

3/28/2018 5:00:00 AM 0

3/28/2018 4:00:00 AM 0

3/28/2018 3:00:00 AM 0

3/28/2018 2:00:00 AM 1

3/28/2018 1:00:00 AM 0

3/28/2018 12:00:00 AM 0

3/27/2018 11:00:00 PM 1

3/27/2018 10:00:00 PM 5

3/27/2018 9:00:00 PM 14869480

3/27/2018 8:00:00 PM 58301868

3/27/2018 7:00:00 PM 63962692

3/27/2018 6:00:00 PM 63202775

3/27/2018 5:00:00 PM 54468916

3/27/2018 4:00:00 PM 56205501

3/27/2018 3:00:00 PM 60133843

3/27/2018 2:00:00 PM 57870581

3/27/2018 1:00:00 PM 55097077

3/27/2018 12:00:00 PM 51064040

3/27/2018 11:00:00 AM 48153309

3/27/2018 10:00:00 AM 43606357

3/27/2018 9:00:00 AM 40656040

3/27/2018 8:00:00 AM 40550632

3/27/2018 7:00:00 AM 10630039

3/27/2018 6:00:00 AM -1129605770

3/27/2018 5:00:00 AM 11516909

3/27/2018 4:00:00 AM 12379970

3/27/2018 3:00:00 AM 13624342

3/27/2018 2:00:00 AM 14176657

3/27/2018 1:00:00 AM 14557260

3/27/2018 12:00:00 AM 14974342

3/26/2018 11:00:00 PM 53768996

3/26/2018 10:00:00 PM 63529902

3/26/2018 9:00:00 PM 64040883

3/26/2018 8:00:00 PM 64084881

3/26/2018 7:00:00 PM 59872717

3/26/2018 6:00:00 PM 63429623

3/26/2018 5:00:00 PM 62662136

3/26/2018 4:00:00 PM 56595986

3/26/2018 3:00:00 PM 51809637

3/26/2018 2:00:00 PM 128283325

3/26/2018 1:00:00 PM -15169709

3/26/2018 12:00:00 PM 196421045

3/26/2018 11:00:00 AM 47786718

3/26/2018 10:00:00 AM 203334117

3/26/2018 9:00:00 AM -38823636

3/26/2018 8:00:00 AM -179249218

3/26/2018 7:00:00 AM 52521284

3/26/2018 6:00:00 AM 10803111

3/26/2018 5:00:00 AM 11558309

3/26/2018 4:00:00 AM 293984273

3/26/2018 3:00:00 AM -55520535

3/26/2018 2:00:00 AM 38137779

3/26/2018 1:00:00 AM 33847934

3/26/2018 12:00:00 AM 14647447

3/25/2018 11:00:00 PM 142753082

3/25/2018 10:00:00 PM 113347224

3/25/2018 9:00:00 PM 130797653

3/25/2018 8:00:00 PM -7374538

3/25/2018 7:00:00 PM 60440266

3/25/2018 6:00:00 PM 101817045

3/25/2018 5:00:00 PM -128784363

3/25/2018 4:00:00 PM -2150243

3/25/2018 3:00:00 PM 120598874

3/25/2018 2:00:00 PM -16331153

3/25/2018 1:00:00 PM 46184685

3/25/2018 12:00:00 PM -1012065

3/25/2018 11:00:00 AM 278837626

3/25/2018 10:00:00 AM 191141694

3/25/2018 9:00:00 AM 38959316

3/25/2018 8:00:00 AM -25447355

3/25/2018 7:00:00 AM 28641794

3/25/2018 6:00:00 AM 11294225

3/25/2018 5:00:00 AM -4835025

3/25/2018 4:00:00 AM 78103888

3/25/2018 3:00:00 AM -93552065

3/25/2018 2:00:00 AM 14382648

3/25/2018 1:00:00 AM 14532767

3/25/2018 12:00:00 AM -80426177

3/24/2018 11:00:00 PM 49522167

3/24/2018 10:00:00 PM 119887680

3/24/2018 9:00:00 PM 61186652

3/24/2018 8:00:00 PM -144640764

3/24/2018 7:00:00 PM 165115689

3/24/2018 6:00:00 PM 199641215

3/24/2018 5:00:00 PM -89590132

3/24/2018 4:00:00 PM -79447042

3/24/2018 3:00:00 PM -23421420

3/24/2018 2:00:00 PM 42001237

3/24/2018 1:00:00 PM 169466940

3/24/2018 12:00:00 PM 46861810

3/24/2018 11:00:00 AM 150542975

3/24/2018 10:00:00 AM 8485307

3/24/2018 9:00:00 AM -2739122

3/24/2018 8:00:00 AM 176677043

3/24/2018 7:00:00 AM 10480950

3/24/2018 6:00:00 AM -62893767

3/24/2018 5:00:00 AM -133126532

3/24/2018 4:00:00 AM 47614182

3/24/2018 3:00:00 AM 57436491

3/24/2018 2:00:00 AM -12755734

3/24/2018 1:00:00 AM 75628394

3/24/2018 12:00:00 AM -58341534

3/23/2018 11:00:00 PM 216174507

3/23/2018 10:00:00 PM 198433093

3/23/2018 9:00:00 PM 64582580

3/23/2018 8:00:00 PM 64737630

3/23/2018 7:00:00 PM -1744730

3/23/2018 6:00:00 PM 55340635

3/23/2018 5:00:00 PM 58788177

3/23/2018 4:00:00 PM 200218097

3/23/2018 3:00:00 PM 58810909

3/23/2018 2:00:00 PM 98132707

3/23/2018 1:00:00 PM -2106992

3/23/2018 12:00:00 PM 35327586

3/23/2018 11:00:00 AM 111814918

3/23/2018 10:00:00 AM 2871714

3/23/2018 9:00:00 AM 29850084

3/23/2018 8:00:00 AM 138007537

3/23/2018 7:00:00 AM -110381540

3/23/2018 6:00:00 AM 11043904

3/23/2018 5:00:00 AM 52426194

3/23/2018 4:00:00 AM 12692708

3/23/2018 3:00:00 AM -228783030

3/23/2018 2:00:00 AM 16111548

3/23/2018 1:00:00 AM 1575605

3/23/2018 12:00:00 AM 2022743

3/22/2018 11:00:00 PM 6893867

3/22/2018 10:00:00 PM 7223540

3/22/2018 9:00:00 PM 25169470

3/22/2018 8:00:00 PM 62216675

3/22/2018 7:00:00 PM 63908403

3/22/2018 6:00:00 PM 63770060

3/22/2018 5:00:00 PM 63360951

3/22/2018 4:00:00 PM 60356179

3/22/2018 3:00:00 PM 60941453

3/22/2018 2:00:00 PM 59310170

3/22/2018 1:00:00 PM 54639217

3/22/2018 12:00:00 PM 53296700

3/22/2018 11:00:00 AM 48592499

3/22/2018 10:00:00 AM 44374366

3/22/2018 9:00:00 AM 41220299

3/22/2018 8:00:00 AM 41015923

3/22/2018 7:00:00 AM 10878178

3/22/2018 6:00:00 AM 10918712

3/22/2018 5:00:00 AM 11815346

3/22/2018 4:00:00 AM 12634989

3/22/2018 3:00:00 AM 13693119

3/22/2018 2:00:00 AM 14511919

3/22/2018 1:00:00 AM 14783738

3/22/2018 12:00:00 AM 15051801

3/21/2018 11:00:00 PM 61461303

3/21/2018 10:00:00 PM 56662480

3/21/2018 9:00:00 PM -490610183

3/21/2018 8:00:00 PM 59235877

3/21/2018 7:00:00 PM 63381478

3/21/2018 6:00:00 PM 57843639

3/21/2018 5:00:00 PM 63092328

3/21/2018 4:00:00 PM 62426793

3/21/2018 3:00:00 PM 60764453

3/21/2018 2:00:00 PM 50949544

3/21/2018 1:00:00 PM 51752389

3/21/2018 12:00:00 PM 1958823

3/21/2018 11:00:00 AM 50061345

3/21/2018 10:00:00 AM 46881706

3/21/2018 9:00:00 AM 38190238

3/21/2018 8:00:00 AM 40875120

3/21/2018 7:00:00 AM 11000429

3/21/2018 6:00:00 AM 10939684

3/21/2018 5:00:00 AM 11760544

3/21/2018 4:00:00 AM 12472156

3/21/2018 3:00:00 AM 13517716

3/21/2018 2:00:00 AM 14402537

3/21/2018 1:00:00 AM 11775829

3/21/2018 12:00:00 AM 15003325

3/20/2018 11:00:00 PM 58889254

3/20/2018 10:00:00 PM 59914764

3/20/2018 9:00:00 PM 60160080

3/20/2018 8:00:00 PM 64718188

3/20/2018 7:00:00 PM 64553714

3/20/2018 6:00:00 PM 59877437

3/20/2018 5:00:00 PM 54941540

3/20/2018 4:00:00 PM -904648454

3/20/2018 3:00:00 PM 57790679

3/20/2018 2:00:00 PM 58578015

3/20/2018 1:00:00 PM 56241055

3/20/2018 12:00:00 PM 52807088

3/20/2018 11:00:00 AM 49089762

3/20/2018 10:00:00 AM 44005376

3/20/2018 9:00:00 AM 40533594

3/20/2018 8:00:00 AM 40376137

3/20/2018 7:00:00 AM 10824305

3/20/2018 6:00:00 AM 10831532

3/20/2018 5:00:00 AM 2159543

3/20/2018 4:00:00 AM 12414496

3/20/2018 3:00:00 AM 13359128

3/20/2018 2:00:00 AM 14256687

3/20/2018 1:00:00 AM 14689524

3/20/2018 12:00:00 AM 14899054

3/19/2018 11:00:00 PM 55843330

3/19/2018 10:00:00 PM 54978278

3/19/2018 9:00:00 PM 63935296

3/19/2018 8:00:00 PM 63625935

3/19/2018 7:00:00 PM 59356840

3/19/2018 6:00:00 PM 56840617

3/19/2018 5:00:00 PM 53233774

3/19/2018 4:00:00 PM 62014386

3/19/2018 3:00:00 PM 60293325

3/19/2018 2:00:00 PM 58332931

3/19/2018 1:00:00 PM 56090767

3/19/2018 12:00:00 PM 53593112

3/19/2018 11:00:00 AM 49383912

3/19/2018 10:00:00 AM 44583376

3/19/2018 9:00:00 AM 40345479

3/19/2018 8:00:00 AM 39961738

3/19/2018 7:00:00 AM 10732375

3/19/2018 6:00:00 AM 10787795

3/19/2018 5:00:00 AM 11401340

3/19/2018 4:00:00 AM 12313523

3/19/2018 3:00:00 AM 13284710

3/19/2018 2:00:00 AM 12818860

3/19/2018 1:00:00 AM 14216994

3/19/2018 12:00:00 AM 14381449

3/18/2018 11:00:00 PM 58503765

3/18/2018 10:00:00 PM 57952531

3/18/2018 9:00:00 PM 56159718

3/18/2018 8:00:00 PM 59869693

3/18/2018 7:00:00 PM 59916093

3/18/2018 6:00:00 PM 56439496

3/18/2018 5:00:00 PM 58047889

3/18/2018 4:00:00 PM 50925404

3/18/2018 3:00:00 PM 54201643

3/18/2018 2:00:00 PM 49515483

3/18/2018 1:00:00 PM 45119428

3/18/2018 12:00:00 PM 36289293

3/18/2018 11:00:00 AM 39136890

3/18/2018 10:00:00 AM 38552350

3/18/2018 9:00:00 AM 38517089

3/18/2018 8:00:00 AM 39988167

3/18/2018 7:00:00 AM 11113584

3/18/2018 6:00:00 AM 11463140

3/18/2018 5:00:00 AM 12104340

3/18/2018 4:00:00 AM 12937827

3/18/2018 3:00:00 AM 13686635

3/18/2018 2:00:00 AM 14165018

3/18/2018 1:00:00 AM 14663434

3/18/2018 12:00:00 AM 14711012

3/17/2018 11:00:00 PM 59744002

3/17/2018 10:00:00 PM 51541615

3/17/2018 9:00:00 PM 58283689

3/17/2018 8:00:00 PM 59363336

3/17/2018 7:00:00 PM 49823763

3/17/2018 6:00:00 PM 59642644

3/17/2018 5:00:00 PM 56045674

3/17/2018 4:00:00 PM 58430802

3/17/2018 3:00:00 PM 47479524

3/17/2018 2:00:00 PM 40812679

3/17/2018 1:00:00 PM 48725436

3/17/2018 12:00:00 PM 42924537

3/17/2018 11:00:00 AM 41086619

3/17/2018 10:00:00 AM 39493105

3/17/2018 9:00:00 AM 39046917

3/17/2018 8:00:00 AM 40022715

3/17/2018 7:00:00 AM 10730768

3/17/2018 6:00:00 AM 11262789

3/17/2018 5:00:00 AM 12078771

3/17/2018 4:00:00 AM 12968131

3/17/2018 3:00:00 AM 13840128

3/17/2018 2:00:00 AM 15035468

3/17/2018 1:00:00 AM 11393516

3/17/2018 12:00:00 AM 11316689

3/16/2018 11:00:00 PM 61509605

3/16/2018 10:00:00 PM 63611302

3/16/2018 9:00:00 PM 63060854

3/16/2018 8:00:00 PM 59419590

3/16/2018 7:00:00 PM 61914750

3/16/2018 6:00:00 PM 57288457

3/16/2018 5:00:00 PM 62301767

3/16/2018 4:00:00 PM 61271151

3/16/2018 3:00:00 PM 55461099

3/16/2018 2:00:00 PM 52790378

3/16/2018 1:00:00 PM 37996895

3/16/2018 12:00:00 PM 47269995

3/16/2018 11:00:00 AM 47548515

3/16/2018 10:00:00 AM 33928056

3/16/2018 9:00:00 AM 40166233

3/16/2018 8:00:00 AM 40193282

3/16/2018 7:00:00 AM 10360314

3/16/2018 6:00:00 AM 10957240

3/16/2018 5:00:00 AM 11649342

3/16/2018 4:00:00 AM 12641114

3/16/2018 3:00:00 AM 13571540

3/16/2018 2:00:00 AM 14235813

3/16/2018 1:00:00 AM 14612982

3/16/2018 12:00:00 AM 15017683

3/15/2018 11:00:00 PM 61475929

3/15/2018 10:00:00 PM 63604429

3/15/2018 9:00:00 PM 58904938

3/15/2018 8:00:00 PM 64015090

3/15/2018 7:00:00 PM 63698423

3/15/2018 6:00:00 PM 58926251

3/15/2018 5:00:00 PM 62034057

3/15/2018 4:00:00 PM 56647581

3/15/2018 3:00:00 PM 60103335

3/15/2018 2:00:00 PM 58164953

3/15/2018 1:00:00 PM 55271066

3/15/2018 12:00:00 PM 51947296

3/15/2018 11:00:00 AM 48124542

3/15/2018 10:00:00 AM 41704154

3/15/2018 9:00:00 AM 40297913

3/15/2018 8:00:00 AM 40409988

3/15/2018 7:00:00 AM 11010631

3/15/2018 6:00:00 AM 11011965

3/15/2018 5:00:00 AM 11729985

3/15/2018 4:00:00 AM 12729794

3/15/2018 3:00:00 AM 13444230

3/15/2018 2:00:00 AM 14299341

3/15/2018 1:00:00 AM 14632468

3/15/2018 12:00:00 AM 14955633

3/14/2018 11:00:00 PM 61462252

3/14/2018 10:00:00 PM 60467312

3/14/2018 9:00:00 PM 62248570

3/14/2018 8:00:00 PM 59864053

3/14/2018 7:00:00 PM 60237427

3/14/2018 6:00:00 PM 62896839

3/14/2018 5:00:00 PM 51468904

3/14/2018 4:00:00 PM 59093019

3/14/2018 3:00:00 PM 60210628

3/14/2018 2:00:00 PM 54498897

3/14/2018 1:00:00 PM 52248015

3/14/2018 12:00:00 PM 51812880

3/14/2018 11:00:00 AM 41411321

3/14/2018 10:00:00 AM 43336631

3/14/2018 9:00:00 AM 40520425

3/14/2018 8:00:00 AM 39495879

3/14/2018 7:00:00 AM 10382409

3/14/2018 6:00:00 AM 10935005

3/14/2018 5:00:00 AM 11625786

3/14/2018 4:00:00 AM 12668213

3/14/2018 3:00:00 AM 13442000

3/14/2018 2:00:00 AM 14183908

3/14/2018 1:00:00 AM 14566456

3/14/2018 12:00:00 AM 14934344

3/13/2018 11:00:00 PM 55085350

3/13/2018 10:00:00 PM 62877817

3/13/2018 9:00:00 PM 62891162

3/13/2018 8:00:00 PM 63413109

3/13/2018 7:00:00 PM 63113759

3/13/2018 6:00:00 PM 57687534

3/13/2018 5:00:00 PM 57039071

3/13/2018 4:00:00 PM 58228577

3/13/2018 3:00:00 PM 57956209

3/13/2018 2:00:00 PM 57009924

3/13/2018 1:00:00 PM 54378065

3/13/2018 12:00:00 PM 50822575

3/13/2018 11:00:00 AM 47165995

3/13/2018 10:00:00 AM 42970027

3/13/2018 9:00:00 AM 40237653

3/13/2018 8:00:00 AM 40097070

Here is the data. PeriodStartTime is a timestamp field.

I tried using the below queries, but no luck:

Sum({<PeriodStartTime={

'$(=Date(Max(PeriodStartTime) ,'MM/DD/YYYY hh:mm:ss tt'))'

}>} setup_defbear)

-

Sum({<PeriodStartTime={

'$(=Date(Max(PeriodStartTime)- 7,'MM/DD/YYYY hh:mm:ss tt'))' ,

'$(=Date(Max(PeriodStartTime)-14,'MM/DD/YYYY hh:mm:ss tt'))' ,

'$(=Date(Max(PeriodStartTime)-21,'MM/DD/YYYY hh:mm:ss tt'))'

}>} setup_defbear)/3

--------------------------------------------------------------------------------------

Sum({<PeriodStartTime={

'$(=Timestamp(Max(PeriodStartTime)))'

}>} setup_defbear)

-

Sum({<PeriodStartTime={

'$(=Timestamp(Max(PeriodStartTime)- 7))' ,

'$(=Timestamp(Max(PeriodStartTime)-14))' ,

'$(=Timestamp(Max(PeriodStartTime)-21))'

}>} setup_defbear)/3

I don't have access to data model. Therefore, I am not able to add fields or create new variables that I can use in set expressions.

Anonymous · ‎2018-04-11

PeriodStartTime Sum(setup_defbear)

4/8/2018 8:00:00 AM 9246658

4/8/2018 7:00:00 AM 6346428

4/8/2018 6:00:00 AM 46473408

4/8/2018 5:00:00 AM 84585658

4/8/2018 4:00:00 AM 15073569

4/8/2018 3:00:00 AM 46090315

4/8/2018 2:00:00 AM 95139995

4/8/2018 1:00:00 AM 59363924

4/8/2018 12:00:00 AM 61808648

4/7/2018 11:00:00 PM 55328041

4/7/2018 10:00:00 PM 52921054

4/7/2018 9:00:00 PM -561720723

4/7/2018 8:00:00 PM 26808781

4/7/2018 7:00:00 PM 26816895

4/7/2018 6:00:00 PM 99666878

4/7/2018 5:00:00 PM 26388497

4/7/2018 4:00:00 PM 97583937

4/7/2018 3:00:00 PM 22231741

4/7/2018 2:00:00 PM 91700755

4/7/2018 1:00:00 PM 50824236

4/7/2018 12:00:00 PM 46220674

4/7/2018 11:00:00 AM 43097937

4/7/2018 10:00:00 AM 41117008

4/7/2018 9:00:00 AM -1697760

4/7/2018 8:00:00 AM 74117297

4/7/2018 7:00:00 AM 79503459

4/7/2018 6:00:00 AM 46185277

4/7/2018 5:00:00 AM 49034462

4/7/2018 4:00:00 AM 17493825

4/7/2018 3:00:00 AM -410364099

4/7/2018 2:00:00 AM 55079998

4/7/2018 1:00:00 AM 62248754

4/7/2018 12:00:00 AM 59045695

4/6/2018 11:00:00 PM 57134765

4/6/2018 10:00:00 PM 101302647

4/6/2018 9:00:00 PM 25844689

4/6/2018 8:00:00 PM 58380486

4/6/2018 7:00:00 PM 67233770

4/6/2018 6:00:00 PM 58825256

4/6/2018 5:00:00 PM 66079696

4/6/2018 4:00:00 PM 56714663

4/6/2018 3:00:00 PM 28222001

4/6/2018 2:00:00 PM 58128493

4/6/2018 1:00:00 PM 54691299

4/6/2018 12:00:00 PM 53825829

4/6/2018 11:00:00 AM 49041193

4/6/2018 10:00:00 AM 44584139

4/6/2018 9:00:00 AM 76168628

4/6/2018 8:00:00 AM 41731495

4/6/2018 7:00:00 AM 43133616

4/6/2018 6:00:00 AM 45607127

4/6/2018 5:00:00 AM 82764995

4/6/2018 4:00:00 AM 49291514

4/6/2018 3:00:00 AM 55802751

4/6/2018 2:00:00 AM 56611912

4/6/2018 1:00:00 AM 61453010

4/6/2018 12:00:00 AM 95479392

4/5/2018 11:00:00 PM 55843972

4/5/2018 10:00:00 PM 57448735

4/5/2018 9:00:00 PM 66293916

4/5/2018 8:00:00 PM 27945686

4/5/2018 7:00:00 PM 99303599

4/5/2018 6:00:00 PM 54108204

4/5/2018 5:00:00 PM 64926084

4/5/2018 4:00:00 PM 30743695

4/5/2018 3:00:00 PM 58666924

4/5/2018 2:00:00 PM 57796261

4/5/2018 1:00:00 PM 24781103

4/5/2018 12:00:00 PM 54102089

4/5/2018 11:00:00 AM 82646729

4/5/2018 10:00:00 AM 44635469

4/5/2018 9:00:00 AM 8698045

4/5/2018 8:00:00 AM 75512616

4/5/2018 7:00:00 AM -379029325

4/5/2018 6:00:00 AM 12673420

4/5/2018 5:00:00 AM 78357254

4/5/2018 4:00:00 AM 81173329

4/5/2018 3:00:00 AM 55687326

4/5/2018 2:00:00 AM 59648148

4/5/2018 1:00:00 AM 62183962

4/5/2018 12:00:00 AM 63605479

4/4/2018 11:00:00 PM 56391060

4/4/2018 10:00:00 PM 60674072

4/4/2018 9:00:00 PM 66715543

4/4/2018 8:00:00 PM 24228690

4/4/2018 7:00:00 PM 56209578

4/4/2018 6:00:00 PM 66512232

4/4/2018 5:00:00 PM 86989355

4/4/2018 4:00:00 PM 97279091

4/4/2018 3:00:00 PM 89222925

4/4/2018 2:00:00 PM 60301160

4/4/2018 1:00:00 PM 57113225

4/4/2018 12:00:00 PM 53915525

4/4/2018 11:00:00 AM 49665022

4/4/2018 10:00:00 AM 76539902

4/4/2018 9:00:00 AM 9460710

4/4/2018 8:00:00 AM 9532624

4/4/2018 7:00:00 AM 43023822

4/4/2018 6:00:00 AM 39680429

4/4/2018 5:00:00 AM 47181813

4/4/2018 4:00:00 AM 50892439

4/4/2018 3:00:00 AM 54327484

4/4/2018 2:00:00 AM 55337235

4/4/2018 1:00:00 AM 61030180

4/4/2018 12:00:00 AM 56792329

4/3/2018 11:00:00 PM 60712353

4/3/2018 10:00:00 PM 50761220

4/3/2018 9:00:00 PM 65959840

4/3/2018 8:00:00 PM 66418451

4/3/2018 7:00:00 PM 58627575

4/3/2018 6:00:00 PM 64765713

4/3/2018 5:00:00 PM 64221932

4/3/2018 4:00:00 PM 61434081

4/3/2018 3:00:00 PM 61842229

4/3/2018 2:00:00 PM 38855792

4/3/2018 1:00:00 PM 57232550

4/3/2018 12:00:00 PM 54918465

4/3/2018 11:00:00 AM 49015645

4/3/2018 10:00:00 AM 44275859

4/3/2018 9:00:00 AM -777278730

4/3/2018 8:00:00 AM 40858434

4/3/2018 7:00:00 AM 42419964

4/3/2018 6:00:00 AM 44749380

4/3/2018 5:00:00 AM 47538132

4/3/2018 4:00:00 AM 49697705

4/3/2018 3:00:00 AM 54946016

4/3/2018 2:00:00 AM 59249099

4/3/2018 1:00:00 AM 61121424

4/3/2018 12:00:00 AM 58941105

4/2/2018 11:00:00 PM 62997058

4/2/2018 10:00:00 PM 62010569

4/2/2018 9:00:00 PM 66020945

4/2/2018 8:00:00 PM 57795993

4/2/2018 7:00:00 PM 62355139

4/2/2018 6:00:00 PM 64737292

4/2/2018 5:00:00 PM 63349703

4/2/2018 4:00:00 PM 58090279

4/2/2018 3:00:00 PM 59763305

4/2/2018 2:00:00 PM 57861760

4/2/2018 1:00:00 PM 54594134

4/2/2018 12:00:00 PM 50777447

4/2/2018 11:00:00 AM 39995003

4/2/2018 10:00:00 AM 42951711

4/2/2018 9:00:00 AM 40631926

4/2/2018 8:00:00 AM -1163847173

4/2/2018 7:00:00 AM 41866364

4/2/2018 6:00:00 AM -1786226309

4/2/2018 5:00:00 AM 45000366

4/2/2018 4:00:00 AM 49523967

4/2/2018 3:00:00 AM 53786810

4/2/2018 2:00:00 AM 56452643

4/2/2018 1:00:00 AM 58800206

4/2/2018 12:00:00 AM 59959459

4/1/2018 11:00:00 PM 52667044

4/1/2018 10:00:00 PM 60493937

4/1/2018 9:00:00 PM 60791173

4/1/2018 8:00:00 PM 55039159

4/1/2018 7:00:00 PM 62349914

4/1/2018 6:00:00 PM -341536648

4/1/2018 5:00:00 PM 61443501

4/1/2018 4:00:00 PM 59490538

4/1/2018 3:00:00 PM 56294555

4/1/2018 2:00:00 PM 52184074

4/1/2018 1:00:00 PM 45736212

4/1/2018 12:00:00 PM 43929351

4/1/2018 11:00:00 AM 39632774

4/1/2018 10:00:00 AM 38828282

4/1/2018 9:00:00 AM 38867979

4/1/2018 8:00:00 AM 40328045

4/1/2018 7:00:00 AM 39369809

4/1/2018 6:00:00 AM 45159184

4/1/2018 5:00:00 AM 47936973

4/1/2018 4:00:00 AM 51864236

4/1/2018 3:00:00 AM 52999697

4/1/2018 2:00:00 AM 55157567

4/1/2018 1:00:00 AM -426852833

4/1/2018 12:00:00 AM 60302550

3/31/2018 11:00:00 PM 60063517

3/31/2018 10:00:00 PM 59606164

3/31/2018 9:00:00 PM 60648637

3/31/2018 8:00:00 PM 61944344

3/31/2018 7:00:00 PM 62165317

3/31/2018 6:00:00 PM 59938217

3/31/2018 5:00:00 PM 61050474

3/31/2018 4:00:00 PM 29825226

3/31/2018 3:00:00 PM 48708246

3/31/2018 2:00:00 PM 53706759

3/31/2018 1:00:00 PM 49560042

3/31/2018 12:00:00 PM 45014870

3/31/2018 11:00:00 AM 41024171

3/31/2018 10:00:00 AM 39724612

3/31/2018 9:00:00 AM 40047457

3/31/2018 8:00:00 AM 40786807

3/31/2018 7:00:00 AM 33748666

3/31/2018 6:00:00 AM 45452105

3/31/2018 5:00:00 AM 48519328

3/31/2018 4:00:00 AM 52406713

3/31/2018 3:00:00 AM 51045451

3/31/2018 2:00:00 AM 57952305

3/31/2018 1:00:00 AM 60713199

3/31/2018 12:00:00 AM 62202956

3/30/2018 11:00:00 PM 62057044

3/30/2018 10:00:00 PM 63381148

3/30/2018 9:00:00 PM 64015788

3/30/2018 8:00:00 PM 64990352

3/30/2018 7:00:00 PM 52415342

3/30/2018 6:00:00 PM 56471019

3/30/2018 5:00:00 PM 64197190

3/30/2018 4:00:00 PM 61262130

3/30/2018 3:00:00 PM 54906330

3/30/2018 2:00:00 PM 55442803

3/30/2018 1:00:00 PM 54425006

3/30/2018 12:00:00 PM 50467794

3/30/2018 11:00:00 AM 46173781

3/30/2018 10:00:00 AM 43054924

3/30/2018 9:00:00 AM -104713619

3/30/2018 8:00:00 AM 41339891

3/30/2018 7:00:00 AM -779772620

3/30/2018 6:00:00 AM 44825129

3/30/2018 5:00:00 AM -378507720

3/30/2018 4:00:00 AM 50646361

3/30/2018 3:00:00 AM 42625947

3/30/2018 2:00:00 AM 58608598

3/30/2018 1:00:00 AM 60880693

3/30/2018 12:00:00 AM 62803417

3/29/2018 11:00:00 PM 62542399

3/29/2018 10:00:00 PM 58819249

3/29/2018 9:00:00 PM 52259748

3/29/2018 8:00:00 PM 66104523

3/29/2018 7:00:00 PM 61636276

3/29/2018 6:00:00 PM 58936066

3/29/2018 5:00:00 PM -482686744

3/29/2018 4:00:00 PM 11688739

3/29/2018 3:00:00 PM 34369315

3/29/2018 2:00:00 PM 276452947

3/29/2018 1:00:00 PM 433375482

3/29/2018 12:00:00 PM 53296446

3/29/2018 11:00:00 AM 313950893

3/29/2018 10:00:00 AM 239556859

3/29/2018 9:00:00 AM 588764915

3/29/2018 8:00:00 AM -258044107

3/29/2018 7:00:00 AM -537107111

3/29/2018 6:00:00 AM 530924495

3/29/2018 5:00:00 AM -536244863

3/29/2018 4:00:00 AM 37349228

3/29/2018 3:00:00 AM 14775018

3/29/2018 2:00:00 AM -312640303

3/29/2018 1:00:00 AM 66252256

3/29/2018 12:00:00 AM 15056031

3/28/2018 11:00:00 PM 53572006

3/28/2018 10:00:00 PM 561032230

3/28/2018 9:00:00 PM -493057719

3/28/2018 8:00:00 PM 228524922

3/28/2018 7:00:00 PM -1019777138

3/28/2018 6:00:00 PM 10218052

3/28/2018 5:00:00 PM 7454295

3/28/2018 4:00:00 PM 3640775

3/28/2018 3:00:00 PM 0

3/28/2018 2:00:00 PM 0

3/28/2018 1:00:00 PM 0

3/28/2018 12:00:00 PM 0

3/28/2018 11:00:00 AM 0

3/28/2018 10:00:00 AM 0

3/28/2018 9:00:00 AM 0

3/28/2018 8:00:00 AM 0

3/28/2018 7:00:00 AM 0

3/28/2018 6:00:00 AM 0

3/28/2018 5:00:00 AM 0

3/28/2018 4:00:00 AM 0

3/28/2018 3:00:00 AM 0

3/28/2018 2:00:00 AM 1

3/28/2018 1:00:00 AM 0

3/28/2018 12:00:00 AM 0

3/27/2018 11:00:00 PM 1

3/27/2018 10:00:00 PM 5

3/27/2018 9:00:00 PM 14869480

3/27/2018 8:00:00 PM 58301868

3/27/2018 7:00:00 PM 63962692

3/27/2018 6:00:00 PM 63202775

3/27/2018 5:00:00 PM 54468916

3/27/2018 4:00:00 PM 56205501

3/27/2018 3:00:00 PM 60133843

3/27/2018 2:00:00 PM 57870581

3/27/2018 1:00:00 PM 55097077

3/27/2018 12:00:00 PM 51064040

3/27/2018 11:00:00 AM 48153309

3/27/2018 10:00:00 AM 43606357

3/27/2018 9:00:00 AM 40656040

3/27/2018 8:00:00 AM 40550632

3/27/2018 7:00:00 AM 10630039

3/27/2018 6:00:00 AM -1129605770

3/27/2018 5:00:00 AM 11516909

3/27/2018 4:00:00 AM 12379970

3/27/2018 3:00:00 AM 13624342

3/27/2018 2:00:00 AM 14176657

3/27/2018 1:00:00 AM 14557260

3/27/2018 12:00:00 AM 14974342

3/26/2018 11:00:00 PM 53768996

3/26/2018 10:00:00 PM 63529902

3/26/2018 9:00:00 PM 64040883

3/26/2018 8:00:00 PM 64084881

3/26/2018 7:00:00 PM 59872717

3/26/2018 6:00:00 PM 63429623

3/26/2018 5:00:00 PM 62662136

3/26/2018 4:00:00 PM 56595986

3/26/2018 3:00:00 PM 51809637

3/26/2018 2:00:00 PM 128283325

3/26/2018 1:00:00 PM -15169709

3/26/2018 12:00:00 PM 196421045

3/26/2018 11:00:00 AM 47786718

3/26/2018 10:00:00 AM 203334117

3/26/2018 9:00:00 AM -38823636

3/26/2018 8:00:00 AM -179249218

3/26/2018 7:00:00 AM 52521284

3/26/2018 6:00:00 AM 10803111

3/26/2018 5:00:00 AM 11558309

3/26/2018 4:00:00 AM 293984273

3/26/2018 3:00:00 AM -55520535

3/26/2018 2:00:00 AM 38137779

3/26/2018 1:00:00 AM 33847934

3/26/2018 12:00:00 AM 14647447

3/25/2018 11:00:00 PM 142753082

3/25/2018 10:00:00 PM 113347224

3/25/2018 9:00:00 PM 130797653

3/25/2018 8:00:00 PM -7374538

3/25/2018 7:00:00 PM 60440266

3/25/2018 6:00:00 PM 101817045

3/25/2018 5:00:00 PM -128784363

3/25/2018 4:00:00 PM -2150243

3/25/2018 3:00:00 PM 120598874

3/25/2018 2:00:00 PM -16331153

3/25/2018 1:00:00 PM 46184685

3/25/2018 12:00:00 PM -1012065

3/25/2018 11:00:00 AM 278837626

3/25/2018 10:00:00 AM 191141694

3/25/2018 9:00:00 AM 38959316

3/25/2018 8:00:00 AM -25447355

3/25/2018 7:00:00 AM 28641794

3/25/2018 6:00:00 AM 11294225

3/25/2018 5:00:00 AM -4835025

3/25/2018 4:00:00 AM 78103888

3/25/2018 3:00:00 AM -93552065

3/25/2018 2:00:00 AM 14382648

3/25/2018 1:00:00 AM 14532767

3/25/2018 12:00:00 AM -80426177

3/24/2018 11:00:00 PM 49522167

3/24/2018 10:00:00 PM 119887680

3/24/2018 9:00:00 PM 61186652

3/24/2018 8:00:00 PM -144640764

3/24/2018 7:00:00 PM 165115689

3/24/2018 6:00:00 PM 199641215

3/24/2018 5:00:00 PM -89590132

3/24/2018 4:00:00 PM -79447042

3/24/2018 3:00:00 PM -23421420

3/24/2018 2:00:00 PM 42001237

3/24/2018 1:00:00 PM 169466940

3/24/2018 12:00:00 PM 46861810

3/24/2018 11:00:00 AM 150542975

3/24/2018 10:00:00 AM 8485307

3/24/2018 9:00:00 AM -2739122

3/24/2018 8:00:00 AM 176677043

3/24/2018 7:00:00 AM 10480950

3/24/2018 6:00:00 AM -62893767

3/24/2018 5:00:00 AM -133126532

3/24/2018 4:00:00 AM 47614182

3/24/2018 3:00:00 AM 57436491

3/24/2018 2:00:00 AM -12755734

3/24/2018 1:00:00 AM 75628394

3/24/2018 12:00:00 AM -58341534

3/23/2018 11:00:00 PM 216174507

3/23/2018 10:00:00 PM 198433093

3/23/2018 9:00:00 PM 64582580

3/23/2018 8:00:00 PM 64737630

3/23/2018 7:00:00 PM -1744730

3/23/2018 6:00:00 PM 55340635

3/23/2018 5:00:00 PM 58788177

3/23/2018 4:00:00 PM 200218097

3/23/2018 3:00:00 PM 58810909

3/23/2018 2:00:00 PM 98132707

3/23/2018 1:00:00 PM -2106992

3/23/2018 12:00:00 PM 35327586

3/23/2018 11:00:00 AM 111814918

3/23/2018 10:00:00 AM 2871714

3/23/2018 9:00:00 AM 29850084

3/23/2018 8:00:00 AM 138007537

3/23/2018 7:00:00 AM -110381540

3/23/2018 6:00:00 AM 11043904

3/23/2018 5:00:00 AM 52426194

3/23/2018 4:00:00 AM 12692708

3/23/2018 3:00:00 AM -228783030

3/23/2018 2:00:00 AM 16111548

3/23/2018 1:00:00 AM 1575605

3/23/2018 12:00:00 AM 2022743

3/22/2018 11:00:00 PM 6893867

3/22/2018 10:00:00 PM 7223540

3/22/2018 9:00:00 PM 25169470

3/22/2018 8:00:00 PM 62216675

3/22/2018 7:00:00 PM 63908403

3/22/2018 6:00:00 PM 63770060

3/22/2018 5:00:00 PM 63360951

3/22/2018 4:00:00 PM 60356179

3/22/2018 3:00:00 PM 60941453

3/22/2018 2:00:00 PM 59310170

3/22/2018 1:00:00 PM 54639217

3/22/2018 12:00:00 PM 53296700

3/22/2018 11:00:00 AM 48592499

3/22/2018 10:00:00 AM 44374366

3/22/2018 9:00:00 AM 41220299

3/22/2018 8:00:00 AM 41015923

3/22/2018 7:00:00 AM 10878178

3/22/2018 6:00:00 AM 10918712

3/22/2018 5:00:00 AM 11815346

3/22/2018 4:00:00 AM 12634989

3/22/2018 3:00:00 AM 13693119

3/22/2018 2:00:00 AM 14511919

3/22/2018 1:00:00 AM 14783738

3/22/2018 12:00:00 AM 15051801

3/21/2018 11:00:00 PM 61461303

3/21/2018 10:00:00 PM 56662480

3/21/2018 9:00:00 PM -490610183

3/21/2018 8:00:00 PM 59235877

3/21/2018 7:00:00 PM 63381478

3/21/2018 6:00:00 PM 57843639

3/21/2018 5:00:00 PM 63092328

3/21/2018 4:00:00 PM 62426793

3/21/2018 3:00:00 PM 60764453

3/21/2018 2:00:00 PM 50949544

3/21/2018 1:00:00 PM 51752389

3/21/2018 12:00:00 PM 1958823

3/21/2018 11:00:00 AM 50061345

3/21/2018 10:00:00 AM 46881706

3/21/2018 9:00:00 AM 38190238

3/21/2018 8:00:00 AM 40875120

3/21/2018 7:00:00 AM 11000429

3/21/2018 6:00:00 AM 10939684

3/21/2018 5:00:00 AM 11760544

3/21/2018 4:00:00 AM 12472156

3/21/2018 3:00:00 AM 13517716

3/21/2018 2:00:00 AM 14402537

3/21/2018 1:00:00 AM 11775829

3/21/2018 12:00:00 AM 15003325

3/20/2018 11:00:00 PM 58889254

3/20/2018 10:00:00 PM 59914764

3/20/2018 9:00:00 PM 60160080

3/20/2018 8:00:00 PM 64718188

3/20/2018 7:00:00 PM 64553714

3/20/2018 6:00:00 PM 59877437

3/20/2018 5:00:00 PM 54941540

3/20/2018 4:00:00 PM -904648454

3/20/2018 3:00:00 PM 57790679

3/20/2018 2:00:00 PM 58578015

3/20/2018 1:00:00 PM 56241055

3/20/2018 12:00:00 PM 52807088

3/20/2018 11:00:00 AM 49089762

3/20/2018 10:00:00 AM 44005376

3/20/2018 9:00:00 AM 40533594

3/20/2018 8:00:00 AM 40376137

3/20/2018 7:00:00 AM 10824305

3/20/2018 6:00:00 AM 10831532

3/20/2018 5:00:00 AM 2159543

3/20/2018 4:00:00 AM 12414496

3/20/2018 3:00:00 AM 13359128

3/20/2018 2:00:00 AM 14256687

3/20/2018 1:00:00 AM 14689524

3/20/2018 12:00:00 AM 14899054

3/19/2018 11:00:00 PM 55843330

3/19/2018 10:00:00 PM 54978278

3/19/2018 9:00:00 PM 63935296

3/19/2018 8:00:00 PM 63625935

3/19/2018 7:00:00 PM 59356840

3/19/2018 6:00:00 PM 56840617

3/19/2018 5:00:00 PM 53233774

3/19/2018 4:00:00 PM 62014386

3/19/2018 3:00:00 PM 60293325

3/19/2018 2:00:00 PM 58332931

3/19/2018 1:00:00 PM 56090767

3/19/2018 12:00:00 PM 53593112

3/19/2018 11:00:00 AM 49383912

3/19/2018 10:00:00 AM 44583376

3/19/2018 9:00:00 AM 40345479

3/19/2018 8:00:00 AM 39961738

3/19/2018 7:00:00 AM 10732375

3/19/2018 6:00:00 AM 10787795

3/19/2018 5:00:00 AM 11401340

3/19/2018 4:00:00 AM 12313523

3/19/2018 3:00:00 AM 13284710

3/19/2018 2:00:00 AM 12818860

3/19/2018 1:00:00 AM 14216994

3/19/2018 12:00:00 AM 14381449

3/18/2018 11:00:00 PM 58503765

3/18/2018 10:00:00 PM 57952531

3/18/2018 9:00:00 PM 56159718

3/18/2018 8:00:00 PM 59869693

3/18/2018 7:00:00 PM 59916093

3/18/2018 6:00:00 PM 56439496

3/18/2018 5:00:00 PM 58047889

3/18/2018 4:00:00 PM 50925404

3/18/2018 3:00:00 PM 54201643

3/18/2018 2:00:00 PM 49515483

3/18/2018 1:00:00 PM 45119428

3/18/2018 12:00:00 PM 36289293

3/18/2018 11:00:00 AM 39136890

3/18/2018 10:00:00 AM 38552350

3/18/2018 9:00:00 AM 38517089

3/18/2018 8:00:00 AM 39988167

3/18/2018 7:00:00 AM 11113584

3/18/2018 6:00:00 AM 11463140

3/18/2018 5:00:00 AM 12104340

3/18/2018 4:00:00 AM 12937827

3/18/2018 3:00:00 AM 13686635

3/18/2018 2:00:00 AM 14165018

3/18/2018 1:00:00 AM 14663434

3/18/2018 12:00:00 AM 14711012

3/17/2018 11:00:00 PM 59744002

3/17/2018 10:00:00 PM 51541615

3/17/2018 9:00:00 PM 58283689

3/17/2018 8:00:00 PM 59363336

3/17/2018 7:00:00 PM 49823763

3/17/2018 6:00:00 PM 59642644

3/17/2018 5:00:00 PM 56045674

3/17/2018 4:00:00 PM 58430802

3/17/2018 3:00:00 PM 47479524

3/17/2018 2:00:00 PM 40812679

3/17/2018 1:00:00 PM 48725436

3/17/2018 12:00:00 PM 42924537

3/17/2018 11:00:00 AM 41086619

3/17/2018 10:00:00 AM 39493105

3/17/2018 9:00:00 AM 39046917

3/17/2018 8:00:00 AM 40022715

3/17/2018 7:00:00 AM 10730768

3/17/2018 6:00:00 AM 11262789

3/17/2018 5:00:00 AM 12078771

3/17/2018 4:00:00 AM 12968131

3/17/2018 3:00:00 AM 13840128

3/17/2018 2:00:00 AM 15035468

3/17/2018 1:00:00 AM 11393516

3/17/2018 12:00:00 AM 11316689

3/16/2018 11:00:00 PM 61509605

3/16/2018 10:00:00 PM 63611302

3/16/2018 9:00:00 PM 63060854

3/16/2018 8:00:00 PM 59419590

3/16/2018 7:00:00 PM 61914750

3/16/2018 6:00:00 PM 57288457

3/16/2018 5:00:00 PM 62301767

3/16/2018 4:00:00 PM 61271151

3/16/2018 3:00:00 PM 55461099

3/16/2018 2:00:00 PM 52790378

3/16/2018 1:00:00 PM 37996895

3/16/2018 12:00:00 PM 47269995

3/16/2018 11:00:00 AM 47548515

3/16/2018 10:00:00 AM 33928056

3/16/2018 9:00:00 AM 40166233

3/16/2018 8:00:00 AM 40193282

3/16/2018 7:00:00 AM 10360314

3/16/2018 6:00:00 AM 10957240

3/16/2018 5:00:00 AM 11649342

3/16/2018 4:00:00 AM 12641114

3/16/2018 3:00:00 AM 13571540

3/16/2018 2:00:00 AM 14235813

3/16/2018 1:00:00 AM 14612982

3/16/2018 12:00:00 AM 15017683

3/15/2018 11:00:00 PM 61475929

3/15/2018 10:00:00 PM 63604429

3/15/2018 9:00:00 PM 58904938

3/15/2018 8:00:00 PM 64015090

3/15/2018 7:00:00 PM 63698423

3/15/2018 6:00:00 PM 58926251

3/15/2018 5:00:00 PM 62034057

3/15/2018 4:00:00 PM 56647581

3/15/2018 3:00:00 PM 60103335

3/15/2018 2:00:00 PM 58164953

3/15/2018 1:00:00 PM 55271066

3/15/2018 12:00:00 PM 51947296

3/15/2018 11:00:00 AM 48124542

3/15/2018 10:00:00 AM 41704154

3/15/2018 9:00:00 AM 40297913

3/15/2018 8:00:00 AM 40409988

3/15/2018 7:00:00 AM 11010631

3/15/2018 6:00:00 AM 11011965

3/15/2018 5:00:00 AM 11729985

3/15/2018 4:00:00 AM 12729794

3/15/2018 3:00:00 AM 13444230

3/15/2018 2:00:00 AM 14299341

3/15/2018 1:00:00 AM 14632468

3/15/2018 12:00:00 AM 14955633

3/14/2018 11:00:00 PM 61462252

3/14/2018 10:00:00 PM 60467312

3/14/2018 9:00:00 PM 62248570

3/14/2018 8:00:00 PM 59864053

3/14/2018 7:00:00 PM 60237427

3/14/2018 6:00:00 PM 62896839

3/14/2018 5:00:00 PM 51468904

3/14/2018 4:00:00 PM 59093019

3/14/2018 3:00:00 PM 60210628

3/14/2018 2:00:00 PM 54498897

3/14/2018 1:00:00 PM 52248015

3/14/2018 12:00:00 PM 51812880

3/14/2018 11:00:00 AM 41411321

3/14/2018 10:00:00 AM 43336631

3/14/2018 9:00:00 AM 40520425

3/14/2018 8:00:00 AM 39495879

3/14/2018 7:00:00 AM 10382409

3/14/2018 6:00:00 AM 10935005

3/14/2018 5:00:00 AM 11625786

3/14/2018 4:00:00 AM 12668213

3/14/2018 3:00:00 AM 13442000

3/14/2018 2:00:00 AM 14183908

3/14/2018 1:00:00 AM 14566456

3/14/2018 12:00:00 AM 14934344

3/13/2018 11:00:00 PM 55085350

3/13/2018 10:00:00 PM 62877817

3/13/2018 9:00:00 PM 62891162

3/13/2018 8:00:00 PM 63413109

3/13/2018 7:00:00 PM 63113759

3/13/2018 6:00:00 PM 57687534

3/13/2018 5:00:00 PM 57039071

3/13/2018 4:00:00 PM 58228577

3/13/2018 3:00:00 PM 57956209

3/13/2018 2:00:00 PM 57009924

3/13/2018 1:00:00 PM 54378065

3/13/2018 12:00:00 PM 50822575

3/13/2018 11:00:00 AM 47165995

3/13/2018 10:00:00 AM 42970027

3/13/2018 9:00:00 AM 40237653

3/13/2018 8:00:00 AM 40097070

Here is the data. PeriodStartTime is a timestamp field.

I tried using the below queries, but no luck:

Sum({<PeriodStartTime={

'$(=Date(Max(PeriodStartTime) ,'MM/DD/YYYY hh:mm:ss tt'))'

}>} setup_defbear)

-

Sum({<PeriodStartTime={

'$(=Date(Max(PeriodStartTime)- 7,'MM/DD/YYYY hh:mm:ss tt'))' ,

'$(=Date(Max(PeriodStartTime)-14,'MM/DD/YYYY hh:mm:ss tt'))' ,

'$(=Date(Max(PeriodStartTime)-21,'MM/DD/YYYY hh:mm:ss tt'))'

}>} setup_defbear)/3

--------------------------------------------------------------------------------------

Sum({<PeriodStartTime={

'$(=Timestamp(Max(PeriodStartTime)))'

}>} setup_defbear)

-

Sum({<PeriodStartTime={

'$(=Timestamp(Max(PeriodStartTime)- 7))' ,

'$(=Timestamp(Max(PeriodStartTime)-14))' ,

'$(=Timestamp(Max(PeriodStartTime)-21))'

}>} setup_defbear)/3

I don't have access to data model. Therefore, I am not able to add fields or create new variables that I can use in set expressions.

Calculating value on same hour/same weekday for past 'n' weeks