New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
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)

• ytd

1 Solution

Accepted Solutions
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
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
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. 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
New Contributor II

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

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

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

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