Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

10 Replies
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

Not applicable
Author

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

  1. 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

Not applicable
Author

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?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
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?

Not applicable
Author

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

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

Not applicable
Author

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!