topic How to create Week On Week report when we don't have consistent data in QlikView

How to create Week On Week report when we don't have consistent data

qlikviewforum — Thu, 24 Oct 2013 17:06:51 GMT

Hi All,

I want to create a Week On Week chart to find out the increase in utlization of space in mounted folder, DB tables and ETL.As per the normal schedule a PROC will run every monday and inserts space details in a table(It inserts once in a week). If everything goes right as per the schedule I can make use of Max(Date) or Max(Week(Date)))to find out space occupied currently and Max(Date)-7 or Max(Week(Date)))-1 to find out space occupied in the previous week.

What if the PROC didn't run on Monday due to some reasons and it ran only on tuesday. Max(Date)-7 or Max(Week(Date)))-1 will not work out as the PROC ran one day later. Also if the PROC ran on tuesday and wednesday, Max(Week(Date)))-1 may give some of of both the dates which will show up double the space.

So can some one help us on how to go about this requirement? I am little confused on how to go about this requirement. Any help on this please?

Regards,

qvforum

Re: How to create Week On Week report when we don't have consistent data

Gysbert_Wassenaar — Thu, 24 Oct 2013 17:29:13 GMT

Assign every PROC run an incremental numeric identifier. Autonumber(Date,'Run') as RunNo would do. You can then use max(RunNo) and max(RunNo)-1 instead.

Re: How to create Week On Week report when we don't have consistent data

Anonymous — Thu, 24 Oct 2013 19:16:00 GMT

When your PROC runs and concatenates that weeks data on, why not store it against the Week() as opposed to the Date() ?

Best Regards, Bill

Re: How to create Week On Week report when we don't have consistent data

qlikviewforum — Fri, 25 Oct 2013 05:46:41 GMT

If the PROC ran on two consective days then how we can use max(RunNo)-1 for Week On Week report? Please advice...

Re: How to create Week On Week report when we don't have consistent data

qlikviewforum — Fri, 25 Oct 2013 05:49:23 GMT

You mean to say every week data I need to concatenate it? Like if the PROC ran twice in a week how do I store only the latest into the QVD? Please attach some application if you can. It will help me...

Re: How to create Week On Week report when we don't have consistent data

qlikviewforum — Fri, 25 Oct 2013 11:16:49 GMT

Now my new plan was to generate 2 below fields,

Year(ReloadTime()) as Year

Week(ReloadTime()) as Week

While storing the QVD I am postfixing it with Year and Month. So we will have single weekly qvd which contains the weekly data. Something like below,

QVDNAME.YYYY_WW

So I load load only those QVD's. Max(Week) and Max(Week)-1 to find the growth.

Now I got stuck on how to load data from latest two weeks data. Please help...

Re: Re: How to create Week On Week report when we don't have consistent data

Anonymous — Fri, 25 Oct 2013 11:45:29 GMT

This script excerpt should, I hope, give you the concept and syntax you are after. You will need to put the bits you into your script & adjust it to your needs

BB :

LOAD * INLINE [

BB Test

];

let vYear = Year(today()) ;

let vWeek1 = Week(today()) ;

let vWeek2 = Week(today()) -1 ;

let vQvdFile1 = 'QVDNAME.' & $(vYear) & '_' & $(vWeek1) & '.qvd' ;

let vQvdFile2 = 'QVDNAME.' & $(vYear) & '_' & $(vWeek2) & '.qvd' ;

Store BB into $(vQvdFile1) (qvd) ;

Store BB into $(vQvdFile2) (qvd) ;

Drop table BB ;

CC1:

load

from $(vQvdFile1) (qvd) ;

CC2:

load

from $(vQvdFile2) (qvd) ;

Best regards, Bill

Re: Re: How to create Week On Week report when we don't have consistent data

qlikviewforum — Fri, 25 Oct 2013 12:36:49 GMT

Thank you. I will update you once I try.

One quick question. I think it may not work if I am trying to load first week of 2013 and last week of 2012 as we are using Year(today()). Please correct me if I am wrong.

Please provide me some other solution if you feel that it wont work as I thought.

Re: How to create Week On Week report when we don't have consistent data

Anonymous — Fri, 25 Oct 2013 13:19:47 GMT

Yup

You will have to careful with the weeks at the end & beginning of Years.

This could help you.

let vYearThisWeek = Year(today()) ;

let vWeekThisWeek = Week(today()) ;

let vYearLastWeek = Year(today()-7) ;

let vWeekLastWeek = Week(today()-7) ;

Best Regards Bill.