topic How to convert Months as dynamic variable in script in QlikView

How to convert Months as dynamic variable in script

Thu, 09 May 2013 22:14:47 GMT

Hello,

I have Excel data set which I want to convert in Qlikview. I am open to suggestion but this is what I am thinking.

Data:

Country	City	Jan11	Feb11	…..	Jan12	Feb12	……	Jan13	Feb13
US	New York	100	20		420	317		37	137
Japn	Tokyo	200	320		440	240		0	200
UK	London	103	240		383	383		383	486
India	Mumbai	140	280		140	0		0	140

Test:

CrossTable(Months, population,28 )

LOAD Country,

City,

concat(Date(AddMonths(Date(Today()),-2),'MMMM') & 'Year(Date(Today(), 'YYYYMMDD')-2)' ), /**JAN 11**/

...wil repeat the formula for every month.

but I know its not smartest way to achieve it.

Above report is Monthly. So every month will be added, and old month will be deleted. (e.g. when June 2013 data is available, May 2011 will be deleted)

can anyone help, what should I do? Some example will help.

Re: How to convert Months as dynamic variable in script

eddysanchez — Thu, 09 May 2013 22:39:02 GMT

First I should create a table with the fieldnames:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

FieldNames:

LOAD 25-RowNo() as ID,Month(AddMonths(Today(),-RowNo()+1))&Right(AddMonths(Today(),-RowNo()+1),2) as FieldName,

Num(Month(AddMonths(Today(),-RowNo()+1))) as Month,

Year(AddMonths(Today(),-RowNo()+1)) as Year

AutoGenerate 24;

Then I should use this table to load your excel dinamically

Re: How to convert Months as dynamic variable in script

Fri, 10 May 2013 06:17:52 GMT

I am not sure if I understood your problem exactly, but here is what i suggest from what i understand..

first, make sure how many months data you need to see in the chart at any given time...

if for example any time u need too see data for recent 6 months, you can probably use this

example latest date is 20130510

LET vMonth1= month(Date(MAX(Datefield)))&'-'& year(max(Datefield)) , this gives you May-2013

LET vMonth2=addmonths(Date(Max(Datefield))-1)&'-'year(admonths(max(Datefield),-1)), this gives you April-2013

LET vMonth6=addmonths(Date(Max(Datefield))-6)&'-'year(admonths(max(Datefield),-6)), this gives you Dec-2012

so when new date is in next month that is 20130610, still the $(vMonth1) gives you Jun-2013 and $(vMonth6) gives Jan-2013

hope this helps....

Re: How to convert Months as dynamic variable in script

Fri, 10 May 2013 14:28:01 GMT

Eddy Sanchez wrote:

Then I should use this table to load your excel dinamically

Can you elobrate on that ? So I build the table. How do I load the data into it.

Will it be something like:

FieldNames:

Load Country

City

? ? ?

FROM

[Trend 2013.xlsx]

(ooxml, embedded labels, table is [Population Trend]);

I am not able to connect dots here. I am still newbee and learning my ways around Qlikview.

Re: How to convert Months as dynamic variable in script

hic — Sun, 12 May 2013 21:54:32 GMT

Not sure I understand what the problem is, but I would load the data this way:

TempData:

CrossTable(Months, population,2)

LOAD * From File.txt;

Data:

LOAD

Country,

City,

Date#(Months,'MMMYY') as Month /* interpret the Month field */

resident TempData;

Drop Table TempData;

Then you will have loaded the months with correct interpretation. If you want to number the months relative today's date, you can add a master calendar that does this:

Calendar:

Load distinct

Month,

12*Year(Month) + Month(Month) - 12*Year(Today()) - Month(Today()) as RelativeMonth

resident Data;

HIC