Tm1py: Extracting data from 2 cubes and using it to do calculations

[jace112233]

I have two cubes, SalePrice and CostPrice. I want to calculate the profit from getting the values from each cube. I used the approach of MDX and I am stuck on the calculation. Is there any approach I can consider to do calculation.

mdx = """
SELECT
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)} ON ROWS,
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Profit_Center] )}, 0)} ON COLUMNS
FROM [CostPrice]
WHERE ([m_OtherCost].[Amount],[Version].[Budget],[Company].[8000])
"""
df = tm1.cubes.cells.execute_mdx_dataframe(mdx)
df.head()

mdx1 = """
SELECT
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)} ON ROWS,
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Profit_Center] )}, 0)} ON COLUMNS
FROM [SalePrice]
WHERE ([m_OtherCost].[Amount],[Version].[Budget],[Company].[8000])
"""

df1 = tm1.cubes.cells.execute_mdx_dataframe(mdx1)
df.head()

(Cost Price)
Month Profit_Center Value
0 Apr-13 70100 1000.0
1 May-13 70100 2000.0
2 Jun-13 70100 9102.0
3 Jun-13 70101 100.0

(SalePrice)
Month Profit_Center Value
0 Apr-13 70100 100000.0
1 May-13 70100 130000.0
2 Jun-13 70100 150000.0
3 Jun-13 70101 160000.0

For example, In Apr-13 in 70100 I will take the value from cost price and sale price to calculate the profit.

[dfrankenCW]

Hi Jace

Give this a try:

1. Combine the Month and the Profit Centre:

df['combo'] = df.apply(lambda x: f"{x['Month']}_{x['Profit_Center']}", axis=1)
df1['combo'] = df1.apply(lambda x: f"{x['Month']}_{x['Profit_Center']}", axis=1)

1. Rename the Value columns

df = df.rename (columns={'Value': 'costprice'})
df1 = df1.rename (columns={'Value': 'salesprice'})

1. Join the tables on the combo

   result = df.set_index('combo').join(df1.set_index('combo'))

2. Do a column calculation based on volumes I assume will come from a 3rd mdx?

From: jace112233 [e-mail]
Sent: Thursday, October 15, 2020 10:08 AM
To: cubewise-code/tm1py [e-mail]
Cc: Subscribed [e-mail]
Subject: [cubewise-code/tm1py] Extracting data from 2 cubes and using it to do calculations (#392)

I have two cubes, SalePrice and CostPrice. I want to calculate the profit from getting the values from each cube. I used the approach of MDX and I am stuck on the calculation. Is there any approach I can consider to do calculation.

mdx = """
SELECT
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)} ON ROWS,
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Profit_Center] )}, 0)} ON COLUMNS
FROM [CostPrice]
WHERE ([m_OtherCost].[Amount],[Version].[Budget],[Company].[8000])
"""
df = tm1.cubes.cells.execute_mdx_dataframe(mdx)
df.head()

mdx1 = """
SELECT
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Month] )}, 0)} ON ROWS,
NON EMPTY {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Profit_Center] )}, 0)} ON COLUMNS
FROM [SalePrice]
WHERE ([m_OtherCost].[Amount],[Version].[Budget],[Company].[8000])
"""

df1 = tm1.cubes.cells.execute_mdx_dataframe(mdx1)
df.head()

(Cost Price)
Month Profit_Center Value
0 Apr-13 70100 1000.0
1 May-13 70100 2000.0
2 Jun-13 70100 9102.0
3 Jun-13 70101 100.0

(SalePrice)
Month Profit_Center Value
0 Apr-13 70100 100000.0
1 May-13 70100 130000.0
2 Jun-13 70100 150000.0
3 Jun-13 70101 160000.0

For example, In Apr-13 in 70100 I will take the value from cost price and sale price to calculate the profit.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHubhttps://github.com/cubewise-code/tm1py/issues/392, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AQGAILPA76LPFF7DHQ4GCLTSK2UXHANCNFSM4SRUMNBA.

[rkvinoth]

Isn't this a Pandas related question? I don't see any connection with TM1py and you should be able to google this stuff.

One sample website:
https://www.datacamp.com/community/tutorials/joining-dataframes-pandas