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

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

Mon, 01 Jun 2015 15:19:37 GMT

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

sunny_talwar — Mon, 01 Jun 2015 15:25:11 GMT

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

Mon, 01 Jun 2015 15:48:52 GMT

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

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

sunny_talwar — Mon, 01 Jun 2015 15:51:27 GMT

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

Tue, 02 Jun 2015 10:01:36 GMT

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

MayilVahanan — Tue, 02 Jun 2015 10:10:27 GMT

Could you able to provide a sample data?

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

sunny_talwar — Tue, 02 Jun 2015 10:16:16 GMT

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

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

Tue, 02 Jun 2015 10:23:08 GMT

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

sunny_talwar — Tue, 02 Jun 2015 10:54:13 GMT

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

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

Tue, 02 Jun 2015 11:03:42 GMT

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

sunny_talwar — Tue, 02 Jun 2015 11:06:45 GMT

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