Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

priyaranjan7563
New Contributor

Calculating value on same hour/same weekday for past 'n' weeks

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?

9 Replies
MVP
MVP

Re: Calculating value on same hour/same weekday for past 'n' weeks

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

MVP
MVP

Re: Calculating value on same hour/same weekday for past 'n' weeks

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:

2018-04-10 10_22_26-Temp - My new sheet (2) _ App overview - Qlik Sense.png

priyaranjan7563
New Contributor

Re: Calculating value on same hour/same weekday for past 'n' weeks

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?

priyaranjan7563
New Contributor

Re: Calculating value on same hour/same weekday for past 'n' weeks

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.

MVP
MVP

Re: Calculating value on same hour/same weekday for past 'n' weeks

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

priyaranjan7563
New Contributor

Re: Calculating value on same hour/same weekday for past 'n' weeks

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

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

MVP
MVP

Re: Calculating value on same hour/same weekday for past 'n' weeks

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

priyaranjan7563
New Contributor

Re: Calculating value on same hour/same weekday for past 'n' weeks

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.

priyaranjan7563
New Contributor

Re: Calculating value on same hour/same weekday for past 'n' weeks

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.

Community Browser