Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Question about average calculation

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!

8 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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!

Not applicable
Author

Please help

chematos
Specialist II
Specialist II

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

Not applicable
Author

  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.

chematos
Specialist II
Specialist II

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

Not applicable
Author

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.

chematos
Specialist II
Specialist II

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