Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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:

  

PAIDNO PAID YET
PeríodoR$ Mercadoria R$ Mercadoria
jan/17sum(value)sum(value)
fev/17sum(value)sum(value)
mar/17sum(value)sum(value)
abr/17sum(value)sum(value)
mai/17sum(value)sum(value)
jun/17sum(value)sum(value)
jul/17sum(value)sum(value)
ago/17sum(value)sum(value)
set/17sum(value)sum(value)
out/17sum(value)sum(value)
nov/17sum(value)sum(value)
dez/17sum(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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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)

View solution in original post

4 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
luismadriz
Specialist
Specialist

Hi,

I think this should help you:

Untitled.png

Assuming data like this:

Untitled.png

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
Not applicable
Author

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.

 

paidnot paid
jan/17450200
fev/1745002150
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
Not applicable
Author

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)