topic Re: Question about average calculation in QlikView

Question about average calculation

Sat, 16 Jun 2012 01:17:33 GMT

Hi,

I have trouble calculating the averages per fiscal year, would anyone help?

This is my case:

I have loaded individial product info at the beginning of fiscal years and end of the fiscal years. But I need to get an average of the two date points, in order to calcalate the percentage for each fiscal year. Does anyone knows how to do this?

Thank you!

Re: Question about average calculation

Anonymous — Mon, 18 Jun 2012 03:56:30 GMT

Hi,

Would you be able to share a document with some sample data to give us an understanding of your data model which would make this a lot easier to answer?

Cheers,

Johannes

Re: Question about average calculation

Mon, 18 Jun 2012 06:01:54 GMT

Hi Johannes,

Thanks for the suggestion.

Here is my case:

I have table A, listing all products at the beginning of fiscal years and end of the fiscal years:

Time Product #

BeginningFY09 1

BeginningFY09 2

BeginningFY09 3

BeginningFY09 4

BeginningFY09 5

BeginningFY09 6

BeginningFY09 7

EndFY09 1

EndFY09 2

EndFY09 3

EndFY09 4

EndFY09 5

EndFY09 6

EndFY09 7

EndFY09 8

EndFY09 9

BeginningFY10 1

BeginningFY10 2

BeginningFY10 3

BeginningFY10 4

BeginningFY10 5

BeginningFY10 6

BeginningFY10 7

EndFY10 1

EndFY10 2

EndFY10 3

EndFY10 4

EndFY10 5

I need to calculate average per each fiscal year, by using (BeginningFY + EndFY)/2, say Count(FY09)=8 and Count(FY10)=6. I don't know how to write a function in order to get an average value for each fiscal year, based on beginning of year and end of year values.

Then in Table B, I have the bad product info.

Time Bad Product #

FY09 1

FY09 3

FY10 2

FY10 6

I want to create a chart based on table B, and calculate the bad product percentage, by using

Bad_Product_Percentage(FY09) = Count(Bad Product #s in Table B of FY09)/Count(FY09) = 2/8=25%

Bad_Product_Percentage(FY10) = Count(Bad Product #s in Table B of FY10)/Count(FY10) = 2/6 = 33%

This can be done under expression tab of the chart, but I don't know how to get the Count(FY09) or Count(FY10), as listed above,

Appreciate your help.

Thank you!

Re: Question about average calculation

Mon, 18 Jun 2012 18:16:23 GMT

Please help

Re: Question about average calculation

chematos — Mon, 18 Jun 2012 18:31:43 GMT

There are a few ways todo that better but in a simple case this should works

Avg would be:

(count({< Time = {'BegginningFY09'}>}distinct Product)+

count({< Time = {'EndFY09'}>}distinct Product))/2

Percentage:

count({< Time = {'BegginningFY09'}>}distinct [Bad Product]) / count(total [Bad Product])

Hope this helps

Re: Question about average calculation

Mon, 18 Jun 2012 19:12:16 GMT

I have another reference table in another tab:

such as

FinancialYearRange:

LOAD * INLINE [

BegginningFY, EndFY, FinancialYearRange

01/01/2009, 12/31/2009, FY-09

01/01/2010, 12/31/2010, FY-10

];

When I wrote the following expression in QlikView,

=(Count(If(<Date

(Time,'MM/DD/YYYY') = {'BegginningFY'}>) DISTINCT Product) + Count(If(<Date(Time,'MM/DD/YYYY') = {'EndFY'}>) DISTINCT Product))/2

It throwed an error. I am not sure where the problem is. QlikView software is not easy to debug.

Re: Question about average calculation

chematos — Mon, 18 Jun 2012 19:20:28 GMT

Time must be a date type field, I´m seeing that the values of Time are words in table A and table B, so what fields do you want to compare??

You must be have a field Time with Dates with the same format that BegginningFY and EndFY.

So, you must have something like this:

Time

18/06/2012

and then, in this expression you have the comparison between 18/06/2012 and your dates on BegginningFY and EndFY fields.

(count({< Time = {"=BegginningFY"}>}distinct Product)+

count({< Time = {"=EndFY"}>}distinct Product))/2

Regards

Re: Question about average calculation

Mon, 18 Jun 2012 19:57:51 GMT

This is totally my mistake. In my qlikview app, the time actually is in time format.

BeginningFY09, EndFY09, BeginningFY10, EndFY10 are all in the date format.

Re: Question about average calculation

chematos — Mon, 18 Jun 2012 20:06:53 GMT

Well, you could use the previous expression:

(count({< Time = {"=BegginningFY"}>}distinct Product)+

count({< Time = {"=EndFY"}>}distinct Product))/2

This is the filter you could use, so you have two dates and you want to count only the products that both dates are the same:

{< Time = {"=BegginningFY"}>}

Use that with the date fields you need