Hi-
I searched high and low and scoured the internet, but couldn't find an answer...
I have the following dimensions: 1) Period, 2) Product, and 3) BusinessUnit
I wanted to have a rule apply a different tax rate to different product rollups. My product rollups are as such:
Product
Cars
Car 1, Car 2, etc.
Vans
Van 1, Van 2, etc.
Planes
Plane 1, Plane 2, etc.
Trains
Train 1, Train 2, etc.
So far, I am able to apply a global tax rate as such:
['TAX_CALC'] = C: ['INCOME_BEFORE_TAX'] * .30
This only applies at the C level, but I can't quite figure out how to make it work at the N level.
I would like to apply different tax rates at different product group levels (e.g. Cars = 25%, Vans = 27%, all else = 30%).
How would I write this at the node level?
Thanks in advance for any help!
Apply Different Tax Rates
-
- Posts: 6
- Joined: Thu Jun 21, 2018 10:10 am
- OLAP Product: IBM Cognos TM1 / PA
- Version: 10.2.2
- Excel Version: Excel 2016
Re: Apply Different Tax Rates
Create an attribute on the product dimension called 'Tax Rate' for instance.
Apply the respective tax rates to the parents such as cars = .25, vans = .27 etc.
The rule will look something like this (assuming cars1, cars2 etc only have 1 parent, "Cars"):
['TAX_CALC']=N: ['INCOME_BEFORE_TAX'] * AttrN('Product', ElPar('Product', !Product, 1), 'Tax Rate');
The consolidation will just be the sum of the income*tax rate at each N level, which is the correct calculation.
Apply the respective tax rates to the parents such as cars = .25, vans = .27 etc.
The rule will look something like this (assuming cars1, cars2 etc only have 1 parent, "Cars"):
['TAX_CALC']=N: ['INCOME_BEFORE_TAX'] * AttrN('Product', ElPar('Product', !Product, 1), 'Tax Rate');
The consolidation will just be the sum of the income*tax rate at each N level, which is the correct calculation.
-
- Posts: 77
- Joined: Wed Sep 12, 2018 11:19 am
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0
- Excel Version: 2016
Re: Apply Different Tax Rates
George is exactly right. Just don't forget your feeders!
['INCOME_BEFORE_TAX'] => ['TAX_CALC']
['INCOME_BEFORE_TAX'] => ['TAX_CALC']
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Apply Different Tax Rates
If these tax rates change frequently or you want to model over versions, over years and the tax rates could differ over time etc, then an alternative would be to add an attribute called type to the product dimension, so each element is either car, van, plane etc and hold the tax rates in a tax rate cube.
Then in the rule db to the tax rate cube, with the element in the db cube being the type attribute of the product.
And don’t forget the feeders!
Hope that makes sense
Cheers, Mark
Then in the rule db to the tax rate cube, with the element in the db cube being the type attribute of the product.
And don’t forget the feeders!
Hope that makes sense
Cheers, Mark
-
- Posts: 9
- Joined: Thu Jun 28, 2018 10:56 pm
- OLAP Product: Planning Analytics
- Version: 10.2
- Excel Version: Excel 2010
Re: Apply Different Tax Rates
Create an attribute on the product dimension called 'Tax Rate' for instance.
I think I was able to do this.
Apply the respective tax rates to the parents such as cars = .25, vans = .27 etc.
Not sure what this means. Where/how do I apply the tax rate?
Thanks.
I think I was able to do this.
Apply the respective tax rates to the parents such as cars = .25, vans = .27 etc.
Not sure what this means. Where/how do I apply the tax rate?
Thanks.
-
- Posts: 77
- Joined: Wed Sep 12, 2018 11:19 am
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0
- Excel Version: 2016
Re: Apply Different Tax Rates
Hi Jrock,
What Mark is saying is to create a separate cube (probably simple with ~2 - 3 dimensions) that has a dimension which contains elements like "car, van, etc.". Then from there you can book the rates to each element. Once that is completed, you can assign an attribute to each element in the product dimension that matches this new dimension (we'll call this Product Category). Then your rule would look like the following:
^ Assuming your new "Rates" cube has 2 dimensions: one for product categories and one for measures.
I would also strongly recommend that you review the general cube design threads that are in this forum. They will provide you with a good foundation to start designing implementations like this in the future.
What Mark is saying is to create a separate cube (probably simple with ~2 - 3 dimensions) that has a dimension which contains elements like "car, van, etc.". Then from there you can book the rates to each element. Once that is completed, you can assign an attribute to each element in the product dimension that matches this new dimension (we'll call this Product Category). Then your rule would look like the following:
Code: Select all
['TAX_CALC']=N: ['INCOME_BEFORE_TAX'] * DB('Rates', AttrS('Product', !Product, 'Product Category'), 'Rate');
I would also strongly recommend that you review the general cube design threads that are in this forum. They will provide you with a good foundation to start designing implementations like this in the future.
-
- Posts: 9
- Joined: Thu Jun 28, 2018 10:56 pm
- OLAP Product: Planning Analytics
- Version: 10.2
- Excel Version: Excel 2010
Re: Apply Different Tax Rates
This worked like a charm. Thanks for the help!