Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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!
Please help
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
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.
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
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.
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