Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Abhi_WP
New Contributor II

Calculating YTD from Monthly data where month is sorted from July to June

Hello,

I want to convert the attached pivot (monthly) data to show in YTD format.  Program months  column is sorted from July to June and is filtered to show only one financial year in the graph. Any help will be appreciated.

 

Expression for sorting the data: - match([Program Month],'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun')

Expression for getting the monthly Actuals: - Sum([Actual Volume])

I am using below expression to calculate the YTD in the graph and is giving me the expected result. Unfortunately the same is not working for pivot.

RangeSum(Above(Sum([Actual Volume]), 0, RowNo()))

 

Thanks

Labels (3)
1 Solution

Accepted Solutions
Abhi_WP
New Contributor II

Re: Calculating YTD from Monthly data where month is sorted from July to June

A colleague suggested to use ColumnNo() instead of RowNo() and it worked.

RangeSum(before(Sum([Actual Volume]), 0, ColumnNo()))

Thanks

6 Replies
Channa
Valued Contributor III

Re: Calculating YTD from Monthly data where month is sorted from July to June

RangeSum(Before(Sum([Actual Volume]), 0, RowNo()))

 

try

 

Channa
Abhi_WP
New Contributor II

Re: Calculating YTD from Monthly data where month is sorted from July to June

Thanks.

 

Still returning 0 for all the months.

YTD Actuals.jpg

 

 

Channa
Valued Contributor III

Re: Calculating YTD from Monthly data where month is sorted from July to June

sum(aggr(RangeSum(above(Sum([Actual Volume]), 0, RowNo())),Programmonth,values))

Channa
Abhi_WP
New Contributor II

Re: Calculating YTD from Monthly data where month is sorted from July to June

Appreciate your help.

Logically, the formula you have provided should work but somehow I am still getting 0s.

sum(aggr(RangeSum(above(Sum([Actual Volume]), 0, RowNo())),[Program Month],
'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'))

 

Regards,

Abhi

Abhi_WP
New Contributor II

Re: Calculating YTD from Monthly data where month is sorted from July to June

A colleague suggested to use ColumnNo() instead of RowNo() and it worked.

RangeSum(before(Sum([Actual Volume]), 0, ColumnNo()))

Thanks

Channa
Valued Contributor III

Re: Calculating YTD from Monthly data where month is sorted from July to June

Try give me sample data i will try or share qvf
Channa