4 Replies Latest reply: Dec 8, 2017 5:37 AM by Matheus Colares

# Two Dimensions in One

Guy,

I need create a Pivot Table with two expression, simple, but not.

I have two dates, the first is the date which is due date and the second is data os payment, and a values what is the value of the note...OK

I have to create de following table:

 PAID NO PAID YET Período R\$ Mercadoria R\$ Mercadoria jan/17 sum(value) sum(value) fev/17 sum(value) sum(value) mar/17 sum(value) sum(value) abr/17 sum(value) sum(value) mai/17 sum(value) sum(value) jun/17 sum(value) sum(value) jul/17 sum(value) sum(value) ago/17 sum(value) sum(value) set/17 sum(value) sum(value) out/17 sum(value) sum(value) nov/17 sum(value) sum(value) dez/17 sum(value) sum(value)

Where this "Período" is just a jan2017, not is nothing with link..

i mean, in expression PAID i have sum all values which date "Due Date" and in expression NOT PAID YET i sum all values which date "Date of Payment" , month by month. I say this because one note can have :

Due Date : 01/01/2017

Date os Paymente: 01/02/2017

Value: \$ 60

Anyone knows how a can make this?

• ###### Re: Two Dimensions in One

Not sure where value \$60 coming from? Can you make one simple table and w.r.t output

• ###### Re: Two Dimensions in One

Hi,

Assuming data like this:

And generating the Months like this:

Data:

Dual(Month(DueDate)&'-'&Year(DueDate),MonthStart(DueDate)) as MonthDueDate,

Dual(Month(PayDate)&'-'&Year(PayDate),MonthStart(PayDate)) as MonthPayDate;

Note,

DueDate,

PayDate,

Value

FROM [lib://AttachedFiles/DuePayDate.xlsx]

(ooxml, embedded labels, table is Sheet1);

I hope this helps,

Cheers,

Luis

• ###### Re: Two Dimensions in One

Hi, thanks  for the feedback.

Anil Babu, this \$60 is just a sample, a data in my data base.

Luis Madriz, it's almost so, but in your exemplo, i have to do like this.

 paid not paid jan/17 450 200 fev/17 4500 2150 mar/17 - 1600 apr/17 - 1000

You understand? This dimension Month don't can be any data load, is just an indicator...

• ###### Re: Two Dimensions in One

I did it.

I create a inline like this:

DATA_GERAL:

DATAGERAL

01/01/2017,

01/02/2017,

01/03/2017,

01/04/2017,

01/05/2017,

01/06/2017,

01/07/2017,

01/08/2017,

01/09/2017,

01/10/2017,

01/11/2017,

01/12/2017,

];

DATA_BASE: