Date Logic

[smilingjohn]

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.

[kaushiknsolanki]

Hi,

Check this qvw file.

Hope this is what you want.

Regards,

Kaushik Solanki

[smilingjohn]

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

[kaushiknsolanki]

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

[smilingjohn]

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

[kaushiknsolanki]

You Should use the Below function.

Avg(Aggr(Count(Date),Bucket)

Regards,

Kaushik Solanki