topic Re: Three months earlier in QlikView

Three months earlier

pgalvezt — Wed, 07 Nov 2012 15:17:23 GMT

Hello,

Currently I'm working on debt models. I'm trying to extract the 3 months earlier with another table. For example If I do clic on Jan2011 I need to get Nov2011, Dic2011,Jan2011. If I do clic On Feb2011 I need to get Dic2011, Jan2011,Feb2011 and so on. How ca I do achieve this?

I left an attached sample for better comprenhension.

Thank You!

Re: Three months earlier

Gysbert_Wassenaar — Wed, 07 Nov 2012 15:34:09 GMT

Create a table like this

ReportMonth, PeriodMonth

Jan2011, Nov2010

Jan2011, Dec2010

Jan2011, Jan2011

Feb2011, Dec2010

Feb2011, Jan2011

Feb2011, Feb2011

etc..

You can then join that table to the other data on PeriodMonth and use ReportMonth to select the Month that determines the period you want to report over.

Re: Three months earlier

pgalvezt — Wed, 07 Nov 2012 16:47:25 GMT

Hi, Thank you for your reply.

In my new table I added periodo field (join with periodo on table T) But The table appears unable. How could avoid this?

I left a new qv sample.

Thank you!

Re: Three months earlier

Gysbert_Wassenaar — Wed, 07 Nov 2012 17:12:14 GMT

I have no idea what's happening. Your Sample.qvw contains no data at all, so I can't tell anything about it.

Re: Three months earlier

pgalvezt — Wed, 07 Nov 2012 17:16:46 GMT

If I reload this qvw will weight 50 MB. Is there another way that you can see the data?

Re: Three months earlier

pgalvezt — Wed, 07 Nov 2012 17:32:01 GMT

Ok! There is.

Re: Three months earlier

Gysbert_Wassenaar — Wed, 07 Nov 2012 17:41:17 GMT

That helps a lot! In table Tarjetas you have field Periodo, but in Report_Table you have no field with that name. It's named PeriodoMes. So qlikview can't find matching field names in the two tables and thus doesn't join them. If you rename PeriodoMes in Periodo the two tables will be joined and you can use ReportMes to select the appropriate months.

edit: Maybe you want to rename PeriodoMes to Fecha_Protesto. Doesn't that field contain the months of the period?

Re: Three months earlier

pgalvezt — Wed, 07 Nov 2012 17:53:53 GMT

I did that but there is a problem with the date maybe.

Re: Three months earlier

Gysbert_Wassenaar — Wed, 07 Nov 2012 18:01:24 GMT

Yes, the formats are different. Make sure the months in Report_Table are the same data type as in the field Fecha_Protesto.

Re: Three months earlier

pgalvezt — Wed, 07 Nov 2012 18:38:42 GMT

Ok I fixed it. Now How can I extract the 3 months? like I explained in my first post?

Thank You!

Re: Three months earlier

Gysbert_Wassenaar — Wed, 07 Nov 2012 18:44:38 GMT

Simply select a date in ReportMes. You will see that three months in Fecha_Protesto are still 'active' (possible). The others are unselected (excluded) and not counted in the calculations. If I understood you correctly, this is what you were trying to achieve. If so, congratulations!

Re: Three months earlier

pgalvezt — Wed, 07 Nov 2012 18:52:18 GMT

Just one more question. How can I left the date using Monthname function inside the Inline Table?

Thank you for your help!

Re: Three months earlier

Gysbert_Wassenaar — Wed, 07 Nov 2012 18:59:03 GMT

I don't understand your question. But perhaps the answer you're looking for is a preceding load.

Report_Table:

Load *, monthname(ReportMes) as ReportMonth

Load * Inline[

ReportMes, Fecha_Protesto

31/01/2011, 30/11/2010

31/01/2011, 31/12/2010

31/01/2011, 31/01/2011

28/02/2011, 31/12/2010

...

];

What happens above is that the results of the inline load are used in the load statement just above it. In this case the monthname function is applied to the ReportMes field to create a new field named ReportMonth. I hope this helps.

Re: Three months earlier

pgalvezt — Wed, 07 Nov 2012 19:16:14 GMT

The clean field format = DD/MM/YYYY

With the MonthName Function gives (Jan 2010, Feb 2010 So on)

In my script I used MonthName. But in Inline Table Doesn't work gives Error.