Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewuser20
Contributor III
Contributor III

Calculate number of month of the current year

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

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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:

Calculate number of month thread273532.jpg

View solution in original post

5 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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:

Calculate number of month thread273532.jpg

qlikviewuser20
Contributor III
Contributor III
Author

Thank you so much ! I it works !

qlikviewuser20
Contributor III
Contributor III
Author

Do you please know how to do this :

i have multiple dimensions with a year_month field and an amount

issue.PNG

And i want to calculate the average just like that :

result.PNG

qlikviewuser20
Contributor III
Contributor III
Author

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