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

[#1]

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

[sunny_talwar]

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

[#3]

Hi Sunny,

Thank you for your answer.

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

[sunny_talwar]

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

[#5]

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 "-"

[MayilVahanan]

Hi

Could you able to provide a sample data?

[sunny_talwar]

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

[#8]

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

[sunny_talwar · Accepted solution]

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:

LOAD OrderLine#,

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

LOAD * Inline [

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

[#10]

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!