8 Replies Latest reply: Jun 18, 2012 4:06 PM by Jose Tos

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

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

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.

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,

Thank you!

• ###### Re: Question about average calculation

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:

Hope this helps

• ###### Re: Question about average calculation

I have another reference table in another tab:

such as

FinancialYearRange:

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

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

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

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