Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2018-04-09 07:25 PM

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?

1,255 Views
0 Likes
9 Replies
petter
Partner - Champion III
Partner - Champion III
‎2018-04-10 04:17 AM

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

1,117 Views
0 Likes
petter
Partner - Champion III
Partner - Champion III
‎2018-04-10 04:26 AM

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

1,117 Views
0 Likes
Anonymous
Not applicable
‎2018-04-10 02:19 PM
Author

In response to petter

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?

1,117 Views
0 Likes
Anonymous
Not applicable
‎2018-04-10 05:48 PM
Author

In response to Anonymous

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.

1,117 Views
0 Likes
petter
Partner - Champion III
Partner - Champion III
‎2018-04-10 08:13 PM

In response to Anonymous

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

1,117 Views
0 Likes
Anonymous
Not applicable
‎2018-04-10 08:56 PM
Author

In response to petter

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

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

1,117 Views
0 Likes
petter
Partner - Champion III
Partner - Champion III
‎2018-04-11 02:00 AM

In response to Anonymous

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

1,117 Views
0 Likes
Anonymous
Not applicable
‎2018-04-11 06:01 PM
Author

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.

1,117 Views
0 Likes
Anonymous
Not applicable
‎2018-04-11 09:59 PM
Author

In response to petter

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.

1,117 Views
0 Likes