topic Re: Need to create a comparison chart based on Period in QlikView

Need to create a comparison chart based on Period

gayatri7 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Need to create a comparison chart based on Period

gayatri7 — Sat, 01 Apr 2017 11:50:43 GMT

Hi Guys,

can anyone help me with this requirement. Its very urgent!!

Re: Need to create a comparison chart based on Period

vinieme12 — Sun, 02 Apr 2017 07:52:46 GMT

please post a sample data that best replicates your scenario.

Are you working with a different data table for each month?

Can you post a sample qvw?

Re: Need to create a comparison chart based on Period

Kushal_Chawda — Sun, 02 Apr 2017 08:47:40 GMT

how your collections are changing? When you select April, 4562 is collection for Mar-16, and when you select Jun, 2400 is collection for Mar-16. Can you please provide he correct input?

Re: Need to create a comparison chart based on Period

gayatri7 — Sun, 02 Apr 2017 16:53:52 GMT

Hi Kushal,,

Yes it will change. Scenario is When I select April month it shows 4562 Rs. has been collected on the month of April for March and when I select June month it shows 2400 Rs has been collected on June for March month. User can pay the premium amount on any month.

I hope it will clarify the scenario.

Re: Need to create a comparison chart based on Period

gayatri7 — Sun, 02 Apr 2017 17:00:49 GMT

Hi Vineeth,

Sample data what I have posted replicates the scenario. Yes, I am working on different data file each month. Every month file will generate which indicates how much premium collection has been done for all the month.for eg. if user has to pay premium amount of his policy in the month of April, he can pay either advance (in the month of March) or late (either on May or june or july etc.) so every month file contains again date column which indicates month for which collection has been done.

I am not able to replicate the same in QVW but I have generated chart in excel and same is attached.

Re: Need to create a comparison chart based on Period

Kushal_Chawda — Mon, 03 Apr 2017 08:14:10 GMT

see the attached file

I am considering that you have collection file for each month with file name like Collection_Apr_2016.xls....

Refer to below script

t1:

LOAD date(Month,'MMM YYYY') as Month, //Monthly Collection

Collection,

date#(replace(mid(FileBaseName(),index(FileBaseName(),subfield(FileBaseName(),'_',2))),'_',' '),'MMM YYYY') as CollectionMonth // Month of excel file

FROM

[..\Collection_*.xlsx]

(ooxml, embedded labels, table is Apr);

t2:

LOAD *,

autonumber(Month) as Pointer

Resident t1

order by Month asc;

DROP Table t1;

Left Join

LOAD Distinct CollectionMonth,

Pointer as PointerCollection

Resident t2

Where CollectionMonth=Month;

t3:

NoConcatenate

LOAD *,

if(len(trim(Collection))=0 or Collection=0,Null(),Pointer-PointerCollection) as FinalPointer

Resident t2;

DROP Table t2;

Note:

In above script goal is to bring CollectionMonth & Month in same format 'MMM YYYY'

Re: Need to create a comparison chart based on Period

gayatri7 — Mon, 03 Apr 2017 08:43:56 GMT

Hi Kushal,

Thank you so much of your time!!!!

Solution looks very good to me. I will try with my data and let you know if any issues.

Thank you so much again.

Thanks

Gayatri

Re: Need to create a comparison chart based on Period

gayatri7 — Mon, 03 Apr 2017 14:38:26 GMT

Hi Kushal,

I have tried replicating the same logic but unable to do that. It is not calculating the "PointerCollection" and FinalPointer both. Could you please see the attached QVW.

Re: Need to create a comparison chart based on Period

Kushal_Chawda — Mon, 03 Apr 2017 17:20:59 GMT

Hi,

File name should be consistent, files you have attached, in which one of the file name is Collection_June_2016, all Month name should be 3 digit i.e in MMM format. Otherwise formatting function Date# , will not work properly

Only one modification in script I have done which is working fine

Left Join (t2)

LOAD Distinct CollectionMonth,

Pointer as PointerCollection

Resident t2

Where Trim(CollectionMonth)=Trim(Month);

I have attached only file name which I have renamed

Re: Need to create a comparison chart based on Period

gayatri7 — Tue, 04 Apr 2017 07:00:35 GMT

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.

Re: Need to create a comparison chart based on Period

Kushal_Chawda — Tue, 04 Apr 2017 07:25:47 GMT

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;

Re: Need to create a comparison chart based on Period

gayatri7 — Tue, 04 Apr 2017 08:35:24 GMT

Hi ,

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

Could you please help.

Re: Need to create a comparison chart based on Period

Kushal_Chawda — Tue, 04 Apr 2017 12:15:55 GMT

which field I need to refer for Collection?

Re: Need to create a comparison chart based on Period

gayatri7 — Tue, 04 Apr 2017 12:25:00 GMT

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

Re: Need to create a comparison chart based on Period

Kushal_Chawda — Tue, 04 Apr 2017 12:38:29 GMT

also do you need these fields for any reasons?

[Fecha Ingreso],

[Fecha Efectiva],

[Fecha Recepción],

[Fecha Cargo],

[Fecha Emisión],

Re: Need to create a comparison chart based on Period

gayatri7 — Tue, 04 Apr 2017 12:41:04 GMT

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)

Re: Need to create a comparison chart based on Period

Kushal_Chawda — Tue, 04 Apr 2017 13:12:36 GMT

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;

Re: Need to create a comparison chart based on Period

gayatri7 — Wed, 05 Apr 2017 06:31:10 GMT

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

Re: Need to create a comparison chart based on Period

Kushal_Chawda — Wed, 05 Apr 2017 06:58:34 GMT

glad that working fine