Discussion Board for collaboration related to Creating Analytics for QlikView.
I have Excel data set which I want to convert in Qlikview. I am open to suggestion but this is what I am thinking.
CrossTable(Months, population,28 )
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.
First I should create a table with the 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
Then I should use this table to load your excel dinamically
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....
Eddy Sanchez wrote:Then I should use this table to load your excel dinamically
Eddy Sanchez wrote:
Can you elobrate on that ? So I build the table. How do I load the data into it.
Will it be something like:
? ? ?
? ? ?
(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.
Not sure I understand what the problem is, but I would load the data this way:
LOAD * From File.txt;
Date#(Months,'MMMYY') as Month /* interpret the Month field */
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:
12*Year(Month) + Month(Month) - 12*Year(Today()) - Month(Today()) as RelativeMonth