
Re: Cost Index calculation problem
Stefan Wühl Aug 29, 2011 3:03 PM (in response to Eliran Sanor)Hi Eliran,
please have a look at attached.
I replaced the fixed years in set analysis with get the max(year) resp max(year)1 from the selection and checking with an if()clause if sum(qty) is larger than zero.
As you see, the entries with missing qty are removed from the table.
Hope this helps,
Stefan

Cost Index Chk2.qvw 177.0 K

Cost Index calculation problem
Eliran Sanor Aug 30, 2011 2:53 AM (in response to Stefan Wühl )Hi Stefan,
First of all, thanks for your help.
Second, My main problem is the calculation itself more then the presentation of it in the table (altought it's important).
If you can see in the attached file you sent, sum of column X isnt equal to the sum of rows.
That's because the calculation doesn't refer to the condition you inserted in the column.
Any thoughts on that issue?
Regards,
Eliran.

Re: Cost Index calculation problem
Stefan Wühl Aug 30, 2011 6:15 AM (in response to Eliran Sanor)Hi Eliran,
sorry for that, I thought that expression total would be doing the job, but here I was totally wrong (because I checked a sum of Qty > zero which then always true in a complete table with all / many products and takes all values again into acount).
Please check attached.
 Change expression total to sum of rows for X/Y column
 changed index calculation column to an advanced aggregation
 check in Excel, that calculations are correct on row level and in total
Regards,
Stefan

Cost Index Chk3.qvw 158.8 K

Re: Cost Index calculation problem
Eliran Sanor Aug 30, 2011 6:41 AM (in response to Stefan Wühl )Hi Stefan,
I can't thank you enough..
The solution worked perfectly!
Thanks and regards,
Eliran.

Re: Cost Index calculation problem
Eliran Sanor Aug 30, 2011 11:31 AM (in response to Stefan Wühl )Stefan,
One more thing if you may.
I want to view the changes in the cost index by Yearly quarters but it's acting little funny when I do it.
Is it the set analysis or the aggr that is interfering?
Please find attached the improved example.
I really appreciate your help.
Regards,Eliran.

Cost Index Chk3 (1).qvw 166.0 K

Cost Index calculation problem
Stefan Wühl Aug 30, 2011 7:22 PM (in response to Eliran Sanor)Eliran,
I had a quick look, but don't find a problem yet. What do you mean with acting little funny.
I added again the two columns for X / Y sums and temporarily also product as second dimension.
The sums seems ok to me, also the cost index calculation ( I double checked in Excel).
Note that in your sample, the total cost index is not the average of the quartely indices. The cost index is always based on the sums X/Y which widely vary over time, so there is a weigh we have to take care of.
Could you point me to the problem?
Regards,
Stefan

Cost Index calculation problem
Eliran Sanor Aug 31, 2011 10:30 AM (in response to Stefan Wühl )Stefan,
When I'm looking at the Cost Index of 2011Q1, When the year is checked I get 7.52%, if I remove the selections from the Year dimnesion, I get for the same quarter 54.79%.
It's acting even funier in my real DB where I miss out quarters when not selecting a specific year while viewing the data by Yearly Quarters. For example, 2010q3,2009q3,2009q4, 2008q4.
Now, 2008 I can blame with lack of data because it's the first year with data in my DB, but it still shows cost index for 2008 quarters which is very bizzare.
Same goes for the example, 2008 quarters calculations shouldnt be shown at all, since there is no data for 2007.
Any idea?
Thanks for the help.
Regards,
Eliran

Cost Index calculation problem
Stefan Wühl Aug 31, 2011 11:44 AM (in response to Eliran Sanor)Got you.
I assume you want also the check for missing Qty to be quarter based now, also the yeartoyear cost index now based on quarterly Sales and Qty?
Need to have a closer look.
Stefan

Cost Index calculation problem
Eliran Sanor Sep 1, 2011 3:16 AM (in response to Stefan Wühl )Exactly..
I'm sorry for not making it clear from the start.
I assumed it will be ok when viewing it this way.
I really apperciate your help.
Regards,
Eliran.

Re: Cost Index calculation problem
Stefan Wühl Sep 2, 2011 10:31 AM (in response to Eliran Sanor)Hi Eliran,
I hope attached comes close to what you want.
Having a dimension of type Date makes it difficult to use Dates in the set expression (because the dimension will not be taken into account in the set expression).
So I created a aggregation table for the avg Prices per product and YearQuarter (only for the periods where the Qty was > 0).
Calculation the cost index is easy then.
This looks ok to me now, please have a look.
Regards,
Stefan

Cost Index Chk4.qvw 211.5 K








