Calculate number of month of the current year

[qlikviewuser20]

Hi,

I wanted to know if there is a solution to do such thing:

Year ,Month ,count_of_month

2015 , Nov  ,   1

2015 , Dec  ,   2

2016, Oct   ,   1

2016 ,Dec   ,  2

Add the count_of_month column to calculate the number of months of the current year but by order ?

Thank you

[Peter_Cammaert]

Script or User Interface?

In the load script you can use an ORDER BY Year, Month and autonumber ‒ QlikView

Don't forget to add your Year field as second parameter to your AutoNumber() call, like

:

AutoNumber(Month, Year) AS count_of_month,

:

This assumes that Month and Year have a numerical date-based dual value.

[Peter_Cammaert · Accepted solution]

For example, this small piece of script code

RawData:

LOAD *, AutoNumber(Month, Year) AS count_of_month;

LOAD Date#(Month, 'MMM') AS Month, Year INLINE

[

Year, Month

2015, Nov

2015, Dec

2016, Oct

2016, Dec

];

produces this in a table box:

[qlikviewuser20]

Thank you so much ! I it works !

[qlikviewuser20]

Do you please know how to do this :

i have multiple dimensions with a year_month field and an amount

And i want to calculate the average just like that :

[qlikviewuser20]

i did find this expression that provides me what i need

Aggr(RangeSum(Above(Sum({< date, MONTH, YEAR_MONTH>} amount), 0, RowNo())),D1,D2, YEAR, YEAR_MONTH) / COUNT_OF_MONTH

but the issue is that my dimensions are dispalyed with a conditional rule so the aggregation need to be dynamic...