10 Replies Latest reply: Jun 2, 2015 7:06 AM by Sunny Talwar

# Set Analysis to have rolling week range with source date format YYYYMMDD

Hi there,

I am trying to design a chart which shows order performance over the last rolling week.

My date Field  (Final.DateRequest) is in the format YYYYMMDD

The functions which I am trying to use to have the rolling weeks are

```date   ( WeekStart(  today(),-1) , 'YYYYMMDD' )
```

```date   ( WeekEnd(  today(),-1) , 'YYYYMMDD' )
```

and (as expected) they yield the correct results, when used separately and not in a Set Analysis context.

When I try to put all together in a Set Analysis statement, it all goes wrong.

I cannot already have a (more simple to my eyes) selection of orders for a specific date, let alone a range.

So a statement like this yield 0 (I have tried all different flavours I could think of, with/without \$, "", '' , with/without = etc)

```count( {1< Final.DateRequest = { "=\$(   =num(date   ( WeekStart(  today(),-1) , 'YYYYMMDD' )   )  )  "} >}  OrderLine# )
```

Anyway, the final result should consider all the Final.DateRequest which are greater/equal than  date ( WeekStart(  today(),-1) , 'YYYYMMDD' ) and smaller than  date ( WeekEnd(  today(),-1) , 'YYYYMMDD' ). Even more difficult than the above

If I go back to basic, and use for example this statement, data is shown correctly (as expected)

```count( {1 <  Final.DateRequest=  {  20150420} >}  OrderLine# )
```

Could someone please be so kind to tell me where and what I am doing wrong?

Thank you

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

Not sure why you are using Num() when you are formatting your expression within curly brackets as Date(..., 'YYYYMMDD'). You are in fact deviating away from your date format and changing it into number which won't match. Try this instead:

=Count({1<Final.DateRequest = {">= \$(Date(WeekStart(Today(), -1), 'YYYYMMDD')) < \$(Date(WeekEnd(Today(), -1), 'YYYYMMDD'))"}>}OrderLine#)

I might have missed a parenthesis here, but other than that, the above should work for you.

Best,

Sunny

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

Hi Sunny,

You are quite right, but I am sure that one mentioned by you is one of the solutions I tried.

I landed in try to use "num" because I was frustrated.

Your solution still does not work.

It yields 0 (not - ) so I assume the expression is rightly written, simply the expression is not yielding any outcome.

This is what upsets me

If I input

```count( {1 <  Final.DateRequest=  {  20150525} >}  OrderLine# )

```

it yields a result

If I input

```date(WeekStart(today()),'YYYYMMDD')
```

I get 20150525

Combining the two, yields nothing

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

My bad, found a small error: Can you try this:

=Count({1<Final.DateRequest = {">= \$(=Date(WeekStart(Today(), -1), 'YYYYMMDD')) < \$(=Date(WeekEnd(Today(), -1), 'YYYYMMDD'))"}>}OrderLine#)

if the above doesn't work then try this and see what happens:

=Count({1<Final.DateRequest = {"\$('>=' & Date(WeekStart(Today(), -1), 'YYYYMMDD') & '<' & Date(WeekEnd(Today(), -1), 'YYYYMMDD'))"}>}OrderLine#)

Best,

Sunny

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

I unfortunately have to say that still is not working.

And I really cannot understand why.

Is there some other check I can do, besides the already mentioned

`count( {1 <  Final.DateRequest=  {  20150525} >}  OrderLine# )    `

`date(WeekStart(today()),'YYYYMMDD')  `

to understand why this Set Analysis is not working as expected?

Results is always 0 with all three proposed methods, rather than "-"

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

Hi

Could you able to provide a sample data?

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

I ll try to reduce file size with Reduce Data function.

In the meantime I am making an interesting discovery, as already following statement does not yield any result

```count( {1<Final.DataRequest = { ">= 20150406 " } > } OrderLine# )
```

meanwhile

```count( {1<Final.DataRequest = { 20150406 } > } OrderLine# )
```

works

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

This one seems to be working:

=Count({1<Final.DateRequest = {"=Final.DateRequest >= Date(WeekStart(Today(), -1), 'YYYYMMDD')"}*{"=Final.DateRequest < Date(WeekEnd(Today(), -1), 'YYYYMMDD')"}>}OrderLine#)

Tested it on a sample application I created with the following script:

Table:

Date#(Final.DateRequest, 'YYYYMMDD') as Final.DateRequest;

OrderLine#, Final.DateRequest

12, 20150521

25, 20150525

32, 20150527

43, 20150601

48, 20150604

54, 20150609

];

Please find attached the sample application for review.

Best,

Sunny

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

I was trying to reduce the QV file size, but didn't succeed in going under the 150 MB..

BUT. It is not needed anymore, your expression works like a charm!!!

Thank you SO MUCH Sunny!

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

No problem , although I still don't understand why the first two expressions don't seem to work. I hope expertscan share some information when they come across this thread.

Best,

Sunny

• ###### Re: Set Analysis to have rolling week range with source date format YYYYMMDD

Ya I think, it would be much easier to troubleshoot if you can share a sample, would it be possible to share one sample?