topic Re: Converting Year-to-Date Pivot Chart into Line Chart in QlikView

Converting Year-to-Date Pivot Chart into Line Chart

lindybrits — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Converting Year-to-Date Pivot Chart into Line Chart

vinieme12 — Mon, 25 Jul 2016 19:16:24 GMT

Can you post sample data?

Re: Converting Year-to-Date Pivot Chart into Line Chart

sunny_talwar — Mon, 25 Jul 2016 19:32:52 GMT

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.

Re: Converting Year-to-Date Pivot Chart into Line Chart

lindybrits — Mon, 25 Jul 2016 19:36:32 GMT

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

Re: Converting Year-to-Date Pivot Chart into Line Chart

lindybrits — Mon, 25 Jul 2016 19:37:42 GMT

Hi! I do indeed have QlikView 12.

Re: Converting Year-to-Date Pivot Chart into Line Chart

sunny_talwar — Mon, 25 Jul 2016 19:45:36 GMT

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

Re: Converting Year-to-Date Pivot Chart into Line Chart

lindybrits — Mon, 25 Jul 2016 19:52:37 GMT

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!

Re: Converting Year-to-Date Pivot Chart into Line Chart

qliksus — Tue, 26 Jul 2016 09:13:28 GMT

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

Re: Converting Year-to-Date Pivot Chart into Line Chart

sunny_talwar — Tue, 26 Jul 2016 09:17:18 GMT

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

Re: Converting Year-to-Date Pivot Chart into Line Chart

lindybrits — Tue, 26 Jul 2016 09:28:46 GMT

Thanks a lot! That is really simple. Appreciated.