topic Re: create a monthcounter in QlikView

create a monthcounter

beck_bakytbek — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: create a monthcounter

adamdavi3s — Mon, 27 Feb 2017 12:28:08 GMT

Have you tried

AutoNumber(TempDate)+1 as MonthCounter,

Re: create a monthcounter

adamdavi3s — Mon, 27 Feb 2017 12:30:07 GMT

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?

Re: create a monthcounter

beck_bakytbek — Mon, 27 Feb 2017 12:32:09 GMT

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

Re: create a monthcounter

beck_bakytbek — Mon, 27 Feb 2017 12:33:14 GMT

i want to have at the beginning Month: Jan

Re: create a monthcounter

adamdavi3s — Mon, 27 Feb 2017 12:35:21 GMT

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

Re: create a monthcounter

beck_bakytbek — Mon, 27 Feb 2017 12:40:15 GMT

Thanks Adam,

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

beck