9 Replies Latest reply on Feb 14, 2017 5:19 AM by Norbert Maijoor

# How to make price ladder per category

hi,

I have a situation where I have to create price ladders per product and per category of products.

i.e.

- the ladders might be low, medium and high;

- each product has 5 levels of category

- I want to see in level 2 where the price of the product is situated

low means 0.7*average price of Level 2

high means 1.3*average price of Level 2

medium is the rest of the population

In my attached example I want to find out in the category "yellow cheese" how each item is classified.

Thanks guys,

Stefan

• ###### 1. Re: How to make price ladder per category

Hi Stefan,

find my approach based on LOD-expression below and stored in attached workbook version 9.3

Average Price of Level 2: {fixed [Classification Level 2]: avg([Price])}

Classified:

if [Price]<=0.7*[Average Price of Level 2] then "Low"

ELSEIF [Price]>=1.3*[Average Price of Level 2] then "High"

else "Medium" END

:

• ###### 2. Re: How to make price ladder per category

Hi Stefan

I've used a LoD calculation to determine the average price per Classification 2 - {FIXED [Classification-Level2]:AVG([Price])}

And then I've created a calculated field called Scale which compares the price against the LoD.

IF [Price] <= 0.7*[Avg Price Per Class2] THEN 'Low'

ELSEIF [Price] >=1.3*[Avg Price Per Class2] THEN 'High'

ELSE 'Medium'

END

Thanks

Donna

PS - If possible always useful to either add a packaged workbook yourself, so we can can a) understand the version of Desktop you're using and b) don't have to recreate the sample data from scratch (or at the least attach the data in an excel file).   In this case it didn't take too long to recreate, but just though worth mentioning for future posts.

1 of 1 people found this helpful
• ###### 3. Re: How to make price ladder per category

ha ha - you beat me to it... good to see we were on the same wavelength :-)

• ###### 4. Re: How to make price ladder per category

Goodmorning Donna,

In the same timezone, same minds;))

• ###### 5. Re: How to make price ladder per category

Thanks, it's working

• ###### 6. Re: How to make price ladder per category

Ha, now I realized that I had to limit the year of analysis.

Can you help me to understand how can I calculate this?

Thanks

• ###### 7. Re: How to make price ladder per category

Hi Stefan,

Just to confirm. Is the "data-structure" as depicted below?

• ###### 8. Re: How to make price ladder per category

Yes, exactly. And now we can consider only 2016, excluding the data for 17.

• ###### 9. Re: How to make price ladder per category

Hi Stefan,

Find my updated approach as reference below and stored in attached workbook version 9.3

{fixed [Classification Level 2],[Year]: avg([Price])}

1 of 1 people found this helpful