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 Guys,

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

[vinieme12]

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?

[Kushal_Chawda]

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?

[gayatri7]

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.

[gayatri7]

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.

[Kushal_Chawda]

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'

[gayatri7]

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

[gayatri7]

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.

[Kushal_Chawda]

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