Help with cumulative option in graphs

[burgersurfer]

I have a graph with 2 axis - one showing the monthly sales and budget information, and on the other scale, I want to show the cumulative total. But, I want the cumulative calculation to stop when there are no succeeding months, so for example, I want the cumulative total from January to current, but for the months August > December, the graph should not show any values.

Attached is an example where the financial year starts in June, so here I want to see June and July, but nothing further.

Any way to do this?

[#2]

Hi,

Plz clear your query once again.

What I understand is:

For bar in Jan, you want total from Jun-Jul-Aug-.....-Dec-Jan

For bar in May, you want from Jun to May.

Am I right?

[shumailh]

I think you can use if condition in your expression to exclude 0 values.

Regards,
Shumail Hussain

[burgersurfer]

Hi Ravi, sorry my description is a bit confusing because our financial year runs June-May

What I need is to show Month-to-date cumulative, for up to the current month.

So in July, the bar should be a sum of June and July, with no information for later months. When we do get to August, I should have a bar for June, July and August (each cumulative) but the remaining months empty.

[#5]

Hi,

Thanks for clarifying doubt.

I hope you want graph for current year and not for the whole database.

If graph is for current year, there cannot be data beyond current date. Neither bar nor line will come for it.

You should use Month-Year format (mmm-yy) on dimension axis. It helps you to separate one financial year from the other.

Just Month won't help you.

If you can identify answer for your query from above, good. Else, share some sample data in QV and your current graph appearing as of now.

[burgersurfer]

Hi Ravi, the thing is I want cumulative, so although there are no figures for August, the cumulative figure is the sum of June and July, which is in some way correct. But that is not what I want to see.

I will try the other approach though, thanks.

[burgersurfer]

THanks Shumail, I tried this expression but did not work:

if(FinTxtMonth > Month(vDateToday), 0, vSales)

but did not work. Will try something else now.

[nathanfurby · Accepted solution]

This is not a great solution as it involves just playing with the colours. But see attached example - have added expression to 'Background color' of SUM chart expression. Not ideal as I said - particularly as you still get pop-up text for the 'hidden' bars.

[burgersurfer]

Looks like that could do the trick, thanks will test!

EDIT: Might not be the ideal solution, but definitely solved my problem, thanks Nathan!

[nathanfurby]

Yes - It may be that you have to come up with your own accumulation code/expression. If you ever do then please post the solution here

No worries and have a good weekend.