Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
eliran
Creator III
Creator III

Cost Index calculation problem

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

10 Replies
swuehl
MVP
MVP

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

eliran
Creator III
Creator III
Author

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.

swuehl
MVP
MVP

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

eliran
Creator III
Creator III
Author

Hi Stefan,

I can't thank you enough..

The solution worked perfectly!

Thanks and regards,

Eliran.

eliran
Creator III
Creator III
Author

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.

swuehl
MVP
MVP

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

eliran
Creator III
Creator III
Author

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

swuehl
MVP
MVP

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

eliran
Creator III
Creator III
Author

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.