Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lindybrits
Contributor III
Contributor III

Converting Year-to-Date Pivot Chart into Line Chart

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!

table.png

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!

1 Solution

Accepted Solutions
sunny_talwar

Something like this?

Capture.PNG

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

View solution in original post

9 Replies
vinieme12
Champion III
Champion III

Can you post  sample data?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
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
Contributor III
Contributor III
Author

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

lindybrits
Contributor III
Contributor III
Author

Hi! I do indeed have QlikView 12.

sunny_talwar

Something like this?

Capture.PNG

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
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

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
Contributor III
Contributor III
Author

Thanks a lot! That is really simple. Appreciated.