Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
matheus_silva
Creator
Creator

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
matheus_silva
Creator
Creator
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

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

matheus_silva
Creator
Creator
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!!!

matheus_silva
Creator
Creator
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