Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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
Highlighted

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:

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

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

Not applicable

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

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

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

Not applicable

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

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

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

Highlighted

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:

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

Not applicable

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!