Converting Year-to-Date Pivot Chart into Line Chart

[lindybrits]

Hi all!

I have been struggling for quite a while with this. I hope someone can help me out.

What I would like to achieve is to show Year-to-Date data in a chart. I have calculated the YTD data seen in the following image, by using the following expression (%MonthNum is 1-12 for Jan-Dec):

RangeSum(Before(Sum({come condition}[Sales Value]), 0, %MonthNum))

This is a modified pivot chart!

I would like to represent this data in a line chart. Two separate line graphs in one chart for 2015 and 2016. I have tried variations on Above/Before, changing the order of importance of the dimensions "Month" and "Year", etc.

Any help would be greatly appreciated!

[vinieme12]

Can you post  sample data?

[sunny_talwar]

Do you happen to have QV12? If you do, you might be able to make this work using sorting available within the Aggr() function (The sortable Aggr function is finally here!‌‌). If you don't, then you will have to rely on sorting in the script.

[lindybrits]

Find attached a simple .csv file with sales values. Please let me know if it is sufficient.

[lindybrits]

Hi! I do indeed have QlikView 12.

[sunny_talwar · Accepted solution]

Something like this?

Expression used:

Aggr(RangeSum(Above(Sum([Sales Value]), 0, %MonthNum)), (Year, (NUMERIC)), (%MonthNum, (Numeric)))

But this will work only in QV12 or above and Qlik Sense

[lindybrits]

That looks great! Its exactly what I need. I'll have a look at the link you sent to understand more about the sortable Aggr function - looks like a very important tool. Thanks a lot for the quick response!

[qliksus]

As this is in the chart we can control the sorting hence you can also make use of the in-built accumulation function to get the result

[sunny_talwar]

Good one, did not realize that we can simplify this. Thanks for sharing this

[lindybrits]

Thanks a lot! That is really simple. Appreciated.