Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Valued Contributor II

Pivot Table(Calendar and Financial Year)

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

7 Replies
Highlighted
MVP
MVP

Re: Pivot Table(Calendar and Financial Year)

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

Highlighted

Re: Pivot Table(Calendar and Financial Year)

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Valued Contributor II

Re: Pivot Table(Calendar and Financial Year)

Hi Tresesco,

Possible to provide a small sample app...?

Highlighted
Valued Contributor II

Re: Pivot Table(Calendar and Financial Year)

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.....

Highlighted
Contributor III

Re: Pivot Table(Calendar and Financial Year)

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

Highlighted
Valued Contributor II

Re: Pivot Table(Calendar and Financial Year)

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...

Highlighted
Not applicable

Re: Pivot Table(Calendar and Financial Year)

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