27 Replies Latest reply: Aug 22, 2018 2:25 PM by Sunny Talwar

# Line Chart: running total with multiple dimensions (month, year)

Dimension1: Month

Dimension2: Year

Measure: Eur_commission (amount)

I can calculate running total with 1 dimension only (month) using the formula:

Rangesum( Above(Sum(eur_commission), 0, RowNo()))

If I add the second dimension (Year), the top chart (no running total) is perfect, the bottom chart (running total)

doesn't work anymore.

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Give this a shot:

Aggr(Rangesum( Above(Sum(eur_commission), 0, RowNo())), Year, Month)

or this

Aggr(Rangesum( Above(Sum(eur_commission), 0, RowNo())), Year, (Month, (Numeric, Ascending)))

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Thanks SunnyT,

In facts, I studied your previous replies to other similar questions and I was expecting your reply.

I tried both solutions and I can't make them working.

I attached a test app if you would like to crack it!

Thanks

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

This?

Expression (which needs actual fields created in the script)

Aggr(RangeSum(Above(Sum(eur_commission), 0, RowNo())), [reg_date.autoCalendar.Year], [reg_date.autoCalendar.Month])

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

This is a very enlightening reply and is fixing many of my issue of newbie.

In facts, I widely use the defined Dimensions  (as shortcut and as easy interface with the lingo of the column names) and probably for this reason many scripts I'm using are failing.

Thank you very much.

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

I am glad I was able to offer help.

Best,

Sunny

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Hello Vicio Giglio ,

Very nice thread . I am lookinng for line chart with two dimensions.  When i  saw your qvf line  cahrt it has two dimensions.  How do we get two dimensions in Line chart.

i am trying to accomplish   this chart  and opened a question in Qlik comminity

Current month Plus previous half year for current year and Previous Year.

Thanks,

Snigdha

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

You can have a single expression with 2 dimensions or you can have multiple expressions with a single dimension... I hope you are not trying to do 2 or more expressions with 2 dimensions?

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Hi Sunny,

I am looking for  2  dimesnions with multiple measures.

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Sunny ,

This is the chart i am trying to accomplish

Current month Plus previous  half year

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

It seems like it can be done with a single dimension... you just might need to use Dual function in your dimension like this

Dual(Month(MonthYear), MonthYear)

It will show just Month, but the underlying value will be MonthYear...

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Hi Sunny,

Dimension is working  but  graph need three lines Budget, Actual for LAST 18 MONTHS A D PRIOR YEAR()2016 HALF YEAR.

PRIOR YEAR ACTUALS LIKE GOING LEFT SIDE  WHILE  BUDGET AND ACTUALS LINES ON RIGHT SIDE

WHICH DOES NOT GIVING GOOD PICTURE TO COMPARE BETWEEN YEARS.

PLEASE LET ME IF THIS IS DOABLE OR NOT

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Would you be able to share a sample file?

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Hi,

I am also struggling with similar issue in qlikview. Could you please send me qlikview file as I am not able to open .qvf file.

When I tried using same formula as mentioned in other replies, it is giving very weird results.

Aggr(RangeSum(Above(Sum(eur_commission), 0, RowNo())), [reg_date.autoCalendar.Year], [reg_date.autoCalendar.Month])

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Which version of QlikView are you using?

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

I am using Qlikview  12

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

QV12 is good....Would you be able to share your expression that you used and images of what you have?

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Hi Sunny,

Objective : Running total by Month for all the years starting from 2008 to 2017 and draw line graph of this cumulative movement (ideally increasing line should be displayed).

Given: Year, Month and Amount

I am using below give formula:

rangesum(Above(Total sum(Paid),0,RowNo(Total)))

this formula is calculating running total by Year rather Months.

If I use :

rangesum(before(Total sum(Paid),0,ColumnNo(Total))),

nothing gets displayed.

I could't share graph or data due to security purpose. Could you please help?

My problem is similar to the one mentioned above by "Vicio Gigli", can you please provide me qlikview file of above solution?

Thanks,

Akhil Sharma

+91 9599785096

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

My friend, I don't have a qlikview file...can you create a sample file and I can show you how to do this may be?

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Hi,

I have created a sample excel file which has similar data that is I am using in qlikview file. I have also created a chart similar to the one I need to create in qlikview.

I have attached QV file as well where I am using below give formula:

rangesum(Above(Total sum(Paid),0,RowNo(Total)))

this formula is calculating running total by Year rather Months (refer Incorrect Cumulative graph).

If I use :

rangesum(before(Total sum(Paid),0,ColumnNo(Total))) (refer Modified Cumulative graph),

nothing gets displayed.

Please suggest what should I write.

Thanks,

Akhil Sharma

+91 9599785096

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

But where is the QlikView file that you created? how can I see the error?

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

I have modified my previous reply, please see qlikview file as well and suggest the correct expression to be written.

Actually correct graph should be like what is plotted in excel file.

If you look at qlikview graph (Incorrect cumulative graph), number is very much different from excel file graph. But excel file graph is correct and qlikview graph should be same to excel file.

Thanks,

Akhil Sharma

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Try this expression

Aggr(RangeSum(Above(Sum(Salary), 0, RowNo())), Year, Month)

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

This is working fine as expected on small data with only limited columns (Year, Month, Salary) but when I added more columns then same formula is not giving expected result. I am not able to understand what is going wrong.

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Add more columns? what columns are you adding?

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Hi Sunny,

I am trying to calculate the cumulative sum(3months) with two dimensions but not getting my line chart to work correctly. It works fine when there is only one dimension.

Chart working as expected:

Dimension-YearMonth

Expression-

Sum(Aggr(

rangesum(above(

if(type='Actuals',
Sum({<[Part type]={'Standard'},[Transaction type]={'Sales'},StdPartRegFlag={'Yes'},assigntest-={'SaaS'} >}Quantity),

if(type='Forecast',
Sum(Forecast),Sum(Forecast)

)

)

,0,3)),YearMonth

))

When I am adding another dimension to it (Product Hierarchy Level 2(It has 4 values)), the chart is getting messed up.

Dimension: YearMonth,Prod Hier Lvl 2

Expression:

Sum(Aggr(

rangesum(above(

if(type='Actuals',
Sum({<[Part type]={'Standard'},[Transaction type]={'Sales'},StdPartRegFlag={'Yes'},assigntest-={'SaaS'},[Product hierarchy lvl 6 desc]-={'None'}>}Quantity),
if(type='Forecast',
Sum({<[Product hierarchy lvl 6 desc]-={'None'}>}Forecast),Sum({<[Product hierarchy lvl 6 desc]-={'None'}>}Forecast)

))

,0,3)),YearMonth,[Prod Hier lvl 2]

))

Can you please help?

• ###### Re: Line Chart: running total with multiple dimensions (month, year)

Hey Sunny,

I was working on a similar issue where I had two dimensions.

While the Aggr(RangeSum(Above(Sum(Measure), 0, RowNo())), [dimension 1], [dimension 2]) was working, which I had been trying before I saw this post, it actually messed up the graph. I see that it worked for Vicio.

In my case what worked your was your first post on Aggr(Rangesum( Above(Sum(measure), 0, RowNo())), dimension 1, (dimension 2, (Numeric, Ascending)))

So thanks a lot .....