Two Dimensions in One

[Anonymous]

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?

[Anil_Babu_Samineni]

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

[luismadriz]

Hi,

I think this should help you:

Assuming data like this:

And generating the Months like this:

Data:

LOAD *,

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

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

LOAD

    Note,

    DueDate,

    PayDate,

    Value

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

(ooxml, embedded labels, table is Sheet1);

I hope this helps,

Cheers,

Luis

[Anonymous]

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

Thanks for all answer!!!

[Anonymous · Accepted solution]

I did it.

I create a inline like this:

DATA_GERAL:

LOAD * INLINE [

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:

LOAD

MONTHNAME(DATAGERAL) AS MESANO

RESIDENT DATA_GERAL;

DROP TABLE DATA_GERAL;

And na expression i did:

SUM({1} IF(MONTHNAME(CPI_DATA_BAIXA) = MESANO, CPI_VALOR)