topic Re: Two Dimensions in One in QlikView

Two Dimensions in One

Anonymous — Wed, 06 Dec 2017 19:47:31 GMT

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

Anil_Babu_Samineni — Thu, 07 Dec 2017 01:38:22 GMT

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

Re: Two Dimensions in One

luismadriz — Thu, 07 Dec 2017 03:34:31 GMT

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

Re: Two Dimensions in One

Anonymous — Thu, 07 Dec 2017 10:52:32 GMT

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

Re: Two Dimensions in One

Anonymous — Fri, 08 Dec 2017 10:37:18 GMT

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)