Need to create a comparison chart based on Period

[gayatri7]

Hi,

I want to make a chart which shows the below details in Qlikview.  For eg.

Data selected for Collection month April

	April
	Month	Collection
-3	Jan-16	24000
-2	Feb-16	2406
-1	Mar-16	4562
0	Apr-16	49000
1	May-16	5000
2	Jun-16	4000
3	Jul-16	2000
4	Aug-16	10
5	Sep-16	5

Data selected for Collection month June

	June
	Month	Collection
	Jan-16
-4	Feb-16	40
-3	Mar-16	2400
-2	Apr-16	24
-1	May-16	3000
0	Jun-16	50000
1	Jul-16	5100
2	Aug-16	3000
3	Sep-16	1000
4	Oct-16	500
5	Nov-16	50
6	Dec-16	5

Now the chart should compare the period wise. if I select Apr for first period it should consider as period 0 and rest month should get calculated accordingly. And if second period is June, June should be consider as Period 0. and chart should compare the period 0 of 1st table to period 0 of second table. As shown below

	Y Axis
X Axis	April	June
-4	0	40
-3	24000	2400
-2	2406	24
-1	4562	3000
0	49000	50000
1	5000	5100
2	4000	3000
3	2000	1000
4	10	500
5	5	50
6	0	5

Attached is the chart created in excel.

[gayatri7]

Hi Kushal,

Thanks for your quick response!!!

I am having another issue. For eg. For collection month April, I am having multiple dates for march month, Pointer variable is creating a separate values for each dates. And at the end I am having final pointer value as -1, -2 and -3 but I want for April collection month , march should be grouped by and consider as -1 only.

As in real time data, I have multiple dates of same month in Collection file. and below is the table created based on real time data.

Sample file with QVD attached.

[Kushal_Chawda]

You need to perform group by in your script

Data:

LOAD *,

         date(Month,'MMM YYYY') as Month

FROM Source;

Final:

noconcatenate

LOAD Poliza,

          Month,

          CollectionMonth,

          Sum(Collection) as Collection

Resident Data

Group by

Poliza,

          Month,

          CollectionMonth;

drop table Data;

[gayatri7]

Hi ,

I tried doing group by but still data is not coming as expected. Attached is the file.

Could you please help.

[Kushal_Chawda]

which field I need to refer for Collection?

[gayatri7]

[Monto Pago] is field in the attahced file which is referring to Collection amount.

[Kushal_Chawda]

also do you need these fields for any reasons?

[Fecha Ingreso],

     [Fecha Efectiva],

[Fecha Recepción],

     [Fecha Cargo],

[Fecha Emisión],

[gayatri7]

No I don't need all those fields. Only I want Month(VCTO_Recibo), Collection Month(from File name), Poliza and [Monto Pago] (Collection amount)

[Kushal_Chawda · Accepted solution]

see this.. 

Póliza and Month has one to one relation. Also can you please send proper input file> or you can attach QVD file

Data:

LOAD Póliza,

     Date(floor(timestamp#([Vcto Recibo],'DD-MM-YYYY hh:mm:ss')),'MMM YYYY') as Month,

     date#(replace(mid(FileBaseName(),index(FileBaseName(),subfield(FileBaseName(),'_',2))),'_',' '),'MMM YYYY') as CollectionMonth,

     [Monto Pago]

FROM

[Recaudacion_Apr_2016.qvd]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Recaudacion:

NoConcatenate

LOAD Póliza,

     sum([Monto Pago]) as [Monto Pago],

     Month,

     CollectionMonth

Resident Data

Group by Póliza,

      Month,

      CollectionMonth;

DROP Table Data;

Rec2:

LOAD *,

    autonumber(Month) as Pointer

Resident Recaudacion

order by Month asc;

DROP Table Recaudacion;

Left Join (Rec2)

LOAD Distinct CollectionMonth,

    Pointer as PointerCollection

Resident Rec2

Where trim(Month)=trim(CollectionMonth);

Rec3:

NoConcatenate

LOAD *,

    if(len(trim([Monto Pago]))=0 or [Monto Pago]=0,Null(),Pointer-PointerCollection) as FinalPointer

Resident Rec2;

DROP Table Rec2;

[gayatri7]

Thank u so much for your time. Its working for me!!!!

[Kushal_Chawda]

glad that working fine