Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to convert Months as dynamic variable in script

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:

CountryCityJan11Feb11…..Jan12Feb12……Jan13Feb13
USNew York10020
420317
37137
JapnTokyo200320
440240
0200
UKLondon103240
383383
383486
IndiaMumbai140280
1400
0140

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. 

4 Replies
eddysanchez
Contributor

Re: How to convert Months as dynamic variable in script

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

Not applicable

Re: How to convert Months as dynamic variable in script

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....

Not applicable

Re: How to convert Months as dynamic variable in script

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.

Employee
Employee

Re: How to convert Months as dynamic variable in script

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

Community Browser