Line chart - current minus previous month

[dia2021]

Hi Everyone,

I have created a line chart based on the cumulative FY dataset, which is working fine. The only problem is how I can show data for October in the attached screenshot.

The trendline is created based on the current minus previous month, but how can I display data for the first month. I need to show the first month's data the way it is without any calc. 

Here is the formula associated with this chart : sum ([sales]) - Above(Sum({<MonthYear>}sales)) * Avg(1)

Any suggestions?

Thank you!

[Or · Accepted solution]

Have you tried putting the entire second part of the formula into an alt? alt(Above(Sum({<MonthYear>}sales)) * Avg(1),0)

[dia2021]

@Or  it worked like a charm!! This is all I wanted. Thanks a lot! 

[dia2021]

@Or  This is currently showing one-year data. What if I include previous year's data, will this formula work in that scenario?

[Or]

All the alt() does is replace a potential null value with 0 - otherwise, what happens is that you're subtracting null from some value of Sum(Sales), and any time you subtract null your result will be null (any mathematical operation involving null results in null). This doesn't really have anything to do with what your specific dimensions or data are, so I would assume this would still work regardless of how many years you have in your chart.

[dia2021]

@Or Thanks for the explanation! This is quite helpful to know about alt function.

[dia2021]

@Or Hi Or, 

I'm trying to modify the same chart. My problem now is that the formula does not work while showing two lines on the line chart for the years 2020 & 2021. It does work for only one dimension.  The formula behind the chart is 

 sum ([Sales]) - Alt(Above(Sum({<MonthYear>}sales)) * Avg(1),0)

I was trying to troubleshoot. After adding the second dimension i.e. Year, I noticed the first half of the formula is working - sum(sales); however, it's not subtracting the second half of the formula after adding another dimension for Year. Any idea how can I fix this?

I appreciate your help.

[Or]

I think Above() respects dimensions, so if you add the year as a dimension, above() won't be able to see Dec 2020 from Jan 2021 because the dimension has changed. You can show two years on a chart (which is what I thought you were asking), but this specific formula won't work if you use year as a dimension. You could try adding the TOTAL qualifier and see if that works - it's not something I've tried to work with much, so I can't be sure.

See the documentation, particularly Example 3, for how above() behaves in the context of multiple dimensions.

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterReco...

[dia2021]

Thanks, @Or, for the explanation! 

I followed up on the link you suggested above and changed the formula, as

sum ([sales]) - Alt(Above(Total(Sum({<Month>}sales))) * Avg(1),0)

I am getting correct numbers on the line chart from October to July; however, it is not providing correct numbers for August and September for both years. 

Note: I am sorting month by expression for the Fiscal Year 

Match([Month], 'October','November','December','January','February','March','April','May','June','July','August','September')

Any idea to fix this?

[Or]

Does it work correctly when you sort the months in natural order? If it does, you could create your own "months" by using e.g. Dual('October',1).

Otherwise, the best advice I can try to give is to switch to using a regular table - Above() in charts defaults to the same behavior as the equivalent table, and it's easier to debug things in a table than it is in a line chart. Then, try to look at the incorrect numbers are, for example - by temporarily removing the sum(Sales) component, and see if the remaining amount matches up with anything else. Ultimately, the formula looks to be correct as written, but without seeing the data it's quite hard to try and debug.