topic Re: Date Logic in QlikView

Date Logic

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Please find the attachment of the qvw file and excel sheet ..

I want to create a table chart and a bar chart with the excel data which is attached by weeks , and years as shown in the screenshot below

basically i want to group the created date , into 1week ,> 1 week , 3month, 1 year and so on .....

Need your help in achieving this

Thanks in Advance.

Re: Date Logic

kaushiknsolanki — Fri, 31 Aug 2018 06:48:16 GMT

Hi,

Check this qvw file.

Hope this is what you want.

Regards,

Kaushik Solanki

Re: Date Logic

smilingjohn — Fri, 31 Aug 2018 06:58:01 GMT

Thanks for the reply Kaushik .

Can you please explain me why used max of created date ?

I dont Understand in the script , when you have droped Delta , then still how can i see all the fileds in the data table ?

what kind of logic is this ..If you can explain it will be really helpfull leaning for me

and with the existing bucked will be able to achive this also ? as shown in the screenshot below ?

Thanks Once again

Re: Date Logic

kaushiknsolanki — Fri, 31 Aug 2018 07:28:34 GMT

Hi,

Let me explain what it does.

Very first thing we need is bucket and for that I have used the days difference between today (In your case the Max Date from data) and created Date.

MaxDate:

Load Max(Created) as MaxDate

Resident Delta;

Let vMaxDate = num(Peek('MaxDate',0,'MaxDate'));

Once we have this days difference we can easily bifurcate them in the expected buckets using the if statement. i,e.

Load *,if(Diff <7,'< 1 Week',

if(Diff <=31,'> 1 Week',

if(Diff <=91,'> 1 Month',

if(Diff <=180,'> 3 Month',

if(Diff <=365,'> 6 Month',

if(Diff <=730,'> 1 Year','> 2 Year')))))) as Bucket;

Now to answer your question on why you see fields even if the table is dropped.

If you notice I have created another table using the existing table i.e Delta., which has the similar data (Fields) plus some additional calculated field like Bucket. So now we would have duplicate data and thus I have dropped the Delta Table, as we dont need it.

Hope this is clear.

Regards,

Kaushik Solanki

Re: Date Logic

smilingjohn — Fri, 31 Aug 2018 09:25:41 GMT

Ho Do i get the Average days , as shown in the screenshot below

Re: Date Logic

kaushiknsolanki — Fri, 31 Aug 2018 16:04:42 GMT

You Should use the Below function.

Avg(Aggr(Count(Date),Bucket)

Regards,

Kaushik Solanki