Pivot Table(Calendar and Financial Year)

[israrkhan]

Hi Guys,

Good Day...!

I created a pivot table, with two dimensions and 5, 6 expressions

my dimensions are Company, Division, and my expressions are Sale, Cost Of Sale, Profit, Expenses, Gross Profit...and so on

every thing is very simple and running as expected.

But my My problem is "one of the company using financial year while other all companies using calendar year"

i have one transaction table, with daily basis transactions.

now in pivot table i want to show financial month and financial year data(for that particular company).

and others all as calendar year, which is already done..!

now let say if i click Feb, my pivot table shows data for Feb for all companies,

but i want to show October data in that company column, financial starting month is September.

i i select Jan, Feb, Mar, then i want to show Sep, Oct, Nov Data for that particular company.

kindly let me know, how to handle this requirement, what will be the best way to achieve this.

Many Thanks

Khan

[tresesco]

One way could be creating a sepearte table for that specific company and linking it through month like:

MasterCal

Month, Link

Jan, 1

...

Dec, 12

CompanyCal

CMonth, Link

Jan, 5

..

Aug, 12

Sep, 1

..

Dec, 4

[PrashantSangle]

Hi,

Easiest way create two chart

one with Calender Year and other is with Financial Year.

Hide/Show chart on Company wise

In chart->property->Layout->show->conditional

Write conditon for hide/Show.

Regards,

[israrkhan]

Hi Tresesco,

Possible to provide a small sample app...?

[israrkhan]

Nice idea, but the user collapse all the column to see the grand total for all companies, and print the report...

:-

any other idea please.....

[Anonymous]

you can add following fields to your master data

if (division='Financial',Month(Transactiondate)) as FinancialMonth

if (division='Financial',Month(Transactiondate)) as OperationMonth

if (division='Financial',Month(Transactiondate)) as BackofficeMonth

if (division='Financial',Month(Transactiondate)) as SalesMonth

and then you can do an inline load of mapping
[MasterMonth, FinancialMonth, OperationMonth, BackofficeMonth ,SalesMonth

Jan,Sep,Jan, Jan,Apr

Feb,Oct,Feb,Feb,May

.

.

.

Dec,Aug,Dec,Dec,Mar];

and while using in the pivot table use Master month

[israrkhan]

Hi  bhagirath

Thanks for the reply....

My Transactions are on AccountCode Level, and each division will have more than 100 accounts, and i can not fix them, because everyday new accounts get created..

Actually my data model is like, Company >> Division >> Sub Division >> Control >> AccountCode

In pivot i am showing only Company and Division,  and transaction are on Account LevelCode...

id user select Year 2015 and Month Feb, so i have to show 2014 Oct data for that specific company.

any other idea please...

[#8]

Hi IsrarKhan

If the structure in the accounts are logical build, maybe it is possible to flag the different account using intervalmatch and then link to the different corresponding calendergroups?

/Teis