topic Re: Cost Index calculation problem in QlikView

Cost Index calculation problem

eliran — Mon, 29 Aug 2011 17:28:21 GMT

Hi all,

I'm intrested in calculate cost index for products.

The forumla is pretty simple,

X=avg price 2011 * qty 2011

Y=avg price 2010 * qty 2011

Cost Index=(X-Y)/X

My problem is as following, If the quantities are equal to 0 (In 2010 or 2011), I don't want it in my caluclation.

The calculation should be dynamic when changing the selected years, so Set analysis will be used.

Another thing, because it's dynamic I can't think of a flag I can use (In the load script) with the set analysis.

Any help will be welcomed.

Attached is a sample of my problem.

Regards,

Eliran.

Re: Cost Index calculation problem

swuehl — Mon, 29 Aug 2011 19:03:57 GMT

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 calculation problem

eliran — Tue, 30 Aug 2011 06:53:31 GMT

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

swuehl — Tue, 30 Aug 2011 10:15:42 GMT

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

Re: Cost Index calculation problem

eliran — Tue, 30 Aug 2011 10:41:04 GMT

Hi Stefan,

I can't thank you enough..

The solution worked perfectly!

Thanks and regards,

Eliran.

Re: Cost Index calculation problem

eliran — Tue, 30 Aug 2011 15:31:57 GMT

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 calculation problem

swuehl — Tue, 30 Aug 2011 23:22:19 GMT

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 — Wed, 31 Aug 2011 14:30:39 GMT

Stefan,

When I'm looking at the Cost Index of 2011-Q1, 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, 2010-q3,2009-q3,2009-q4, 2008-q4.

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

swuehl — Wed, 31 Aug 2011 15:44:00 GMT

Got you.

I assume you want also the check for missing Qty to be quarter based now, also the year-to-year cost index now based on quarterly Sales and Qty?

Need to have a closer look.

Stefan

Cost Index calculation problem

eliran — Thu, 01 Sep 2011 07:16:50 GMT

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

swuehl — Fri, 02 Sep 2011 14:31:03 GMT

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