create a monthcounter

[beck_bakytbek]

Hi Folks,

i have one question, namely regarding a mothcounter in master calender, i want to have a numbered consecutively lilke

Jan, Feb, Mrz. Apr, although Jan = 0, Feb= 25,

in my example (see attached) my orders begins with Month: Feb,

for this issue, i use this code:

QuarterMap:

Mapping LOAD

RowNo() as Month,

'Q' & Ceil(RowNo()/3) as Quarter

AutoGenerate (12);

Temp:

LOAD

min(Datum) as minDate,

max(Datum) as maxDate

Resident main;

Let varMinDate = num(Peek('minDate',0,'Temp'));

Let varMaxDate = num(Peek('maxDate',0,'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + IterNo()-1 as Num,

Date($(varMinDate)+IterNo()-1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo()-1 <= $(varMaxDate);

[Master Calendar]:

LOAD

    TempDate as Datum,

    AutoNumber(TempDate) as MonthCounter,

    Week(TempDate) as Week,

    Year(TempDate) as Year,

    Month(TempDate) as Dim_Month,

    Day(TempDate) as Day,

    ApplyMap('QuarterMap',month(TempDate),Null()) as Quarter,

    Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

    WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

how can i resolve this issue? Thanks a lot in advance

beck

[adamdavi3s]

Have you tried

AutoNumber(TempDate)+1 as MonthCounter,

[adamdavi3s]

Actually why doesn't this work for you?

Autonumber is 1 based, so the first number will be 1 so Feb will be 1 which is what you want?

[beck_bakytbek]

Hi Adam, first of all, thank you very much for your help,

i tried your suggestion, but i got the same results, my order starts with Feb, without Jan at the beginning.

thanks a lot

beck

[beck_bakytbek]

i want to have at the beginning Month: Jan

[adamdavi3s]

Hi Beck,

In that case you would need to add a dummy row of data for January or it won't show, it looks like you're using Qliksense (this is the Qlikview forum) so I am not sure what options you have in the front end to show all dimensions etc.

Generating Missing Data In QlikView

[beck_bakytbek]

Thanks Adam,

i am trying to find the solution, but thanks for your feedback

beck