topic Re: days difference and average calculation in QlikView

days difference and average calculation

Anonymous — Mon, 19 Jan 2015 10:53:10 GMT

hi gurus,

I Have table Like Below

Creation_Date Approved_Date Count Days Difference AverageDays

01/01/2012 01/10/2012 1 10 10/1=1

01/01/2012 01/01/2012 1 0(but need to populate 1) 1/1=1

01/01/2012 31/12/2011 1 approved >creation(Eliminate from days diff calculation) -

days difference calculation:

if (creation_date -Approved_Date )=0,need to populate 1

and if (Approved_Date >creation_date need to eliminate ,remaining rows days Difference need to calculate

How to write expression for Days Difference

Re: days difference and average calculation

Anonymous — Mon, 19 Jan 2015 18:25:46 GMT

Hello,

do you need this in script editor or in a chart?

For script:

data:

load * Inline [

Creation_Date, Approved_Date, Count

01/01/2012, 10/01/2012, 1

01/01/2012, 01/01/2012, 1

01/01/2012, 31/12/2011, 1

];

new_data:

load

Creation_Date,

Approved_Date,

if(Approved_Date < Creation_Date, null(), interval(Approved_Date-Creation_Date, 'd')+1) as [Days Difference],

if(Approved_Date < Creation_Date, null(), ((interval(Approved_Date-Creation_Date,'d')+1)/Count)) as AverageDays

resident data;

drop Table data;

Re: days difference and average calculation

Anonymous — Tue, 20 Jan 2015 10:20:57 GMT

thanks for your time.

i need this in expression level.because Two fields coming from different tables.

conditions:

1.if(days difference =0 ,then need to populate 1

2.if(days difference >0 ,then days difference example 10,11 days

3.need to sum the days after populating 0 with 1

How to do this

Re: days difference and average calculation

Anonymous — Tue, 20 Jan 2015 10:29:47 GMT

Hello,

can this attachemnt be ok?

Let me know!!

Re: days difference and average calculation

Anonymous — Tue, 20 Jan 2015 10:53:26 GMT

hi thanks for your Time,

Not always days difference+1 ,if (days difference is >0, actual dys difference i need to show .

i need to sumup all these days difference and divided by count

iam using like this: am i right

sum(if(Approved_Date<Creation_Date, '-',

if(Approved_Date-Creation_Date=0,1,Interval(Approved_Date-Creation_Date,'d'))))

Re: days difference and average calculation

Anonymous — Tue, 20 Jan 2015 10:59:54 GMT

So it's not correct 10, but 9 for the first couple of date?

If so you can add an if statement like this for expression Difference that ca be consider the case equal date:

(Approved_Date<Creation_Date, null(), if(Approved_Date=Creation_Date, 1,Interval(Approved_Date-Creation_Date,'d')))

Post your expected output, please! Post the resulting table you would have.