Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
Not sure where value $60 coming from? Can you make one simple table and w.r.t output
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
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!!!
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)