11 Replies Latest reply: Nov 13, 2015 7:53 AM by Ruben Marin

# How to get rid of descending chart lines

Hi,

hopefully I'm able to explain, what I mean

If no period is chosen, is it possible to show only completed weeks (or in this case month) in charts to avoid these descending lines at the end which looks not very hopeful

Thanks in advance!

Oliver

• ###### Re: How to get rid of descending chart lines

I suggest a Set Analysis statement.

Let's assume this chart is the result of the dimension Month and the expression sum(Sales). Change that expression to this:

=sum({ < Month -= {\$(=month(today()))} > } Sales)

That "minus equal" (-=) thing means "take the current selection but exclude this value".

The alternative solution of course is... make more sales .

• ###### Re: How to get rid of descending chart lines

Hi Onno,

The alternative solution of course is... make more sales .

yeah, but this would mean, we have to make more sales in the first days of this month than of the hole last month - would be great

I tried your solution, it works if I select January til November. If I deselect all, I get all data except November

sum({ < CAL_Month -= {\$(=month(today()))} > } POS_Gesamt_Umsatz_EUR)

• ###### Re: How to get rid of descending chart lines

One more..

=Sum({<Month={"<=\$(=Month(AddMonths(today(),-1)))"}>} Sales)

• ###### Re: How to get rid of descending chart lines

I tried it with our variables but get the same result

=Sum({<CAL_Month={"<=\$(=CAL_Month(AddMonths(today(),-1)))"}>} POS_Gesamt_Umsatz_EUR)

• ###### Re: How to get rid of descending chart lines

It should be like below

=Sum({<CAL_Month={"<=\$(=Month(AddMonths(today(),-1)))"}>} POS_Gesamt_Umsatz_EUR)

• ###### Re: How to get rid of descending chart lines

Thanks a lot for the quick response!

My problem with set analysis is, I don't have a single expression like "Sales".

My sales formula looks like

SUM(IF(POS_Belart_RGA = 'RuG',POS_Gesamt_Umsatz_EUR,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_1_Netto,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_2_Netto,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_3_Netto,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_4_Netto,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_5_Netto,0))

and I never was able to put this into a set analysis expression without errors.

So...

Sum({<CAL_Month={"<=\$(=CAL_Month(AddMonths(today(),-1)))"}>}

SUM(IF(POS_Belart_RGA = 'RuG',POS_Gesamt_Umsatz_EUR,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_1_Netto,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_2_Netto,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_3_Netto,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_4_Netto,0))+

SUM(IF(POS_Belart_RGA = 'RuG',BEL_Umsatz_Nebenkosten_5_Netto,0)))

gives no data back.

Sorry, I'm not able to explain my problem better.

• ###### Re: How to get rid of descending chart lines

This looks a bit as if it could be useful to move some of these calculations to your script. It would sure help for performance and the nice side effect is that your (set analysis) expression in the front end will be a lot easier.

Worth considering?

• ###### Re: How to get rid of descending chart lines

Yes, I think, you're right. I tried to do the calculation in the script, but because the sales of articles and the additional costs are in different data bases, I was not able.

I tried to build an extra table (SalesTotals), but didn't get the correct results so thats my workaround which gives me the correct sales numbers.

I would be glad, to fix this issue, but I think that needs a lot more than this community is able to do without deeper insights in our data base.

Perhaps I take your answer as a motivation to try it once more

• ###### Re: How to get rid of descending chart lines

HI,

Try some thing like,

=Sum({<CAL_Month={"<=\$(=CAL_Month(AddMonths(today(),-1)))"},POS_Belart_RGA ={ 'RuG'}>}

(POS_Gesamt_Umsatz_EUR+BEL_Umsatz_Nebenkosten_1_Netto+BEL_Umsatz_Nebenkosten_2_Netto+

BEL_Umsatz_Nebenkosten_3_Netto+BEL_Umsatz_Nebenkosten_4_Netto+BEL_Umsatz_Nebenkosten_5_Netto))

• ###### Re: How to get rid of descending chart lines

Maybe:

Sum({<CAL_Month={"<=\$(=CAL_Month(AddMonths(today(),-1)))"}, POS_Belart_RGA={'RuG'}>}

RangeSum(POS_Gesamt_Umsatz_EUR, BEL_Umsatz_Nebenkosten_1_Netto, ...., BEL_Umsatz_Nebenkosten_5_Netto))

Better if you use the IsCompletedMonth flag as Henric says.

• ###### Re: How to get rid of descending chart lines

The by far simplest solution is to include a flag in your Master Calendar in the script:

If( Date < MonthStart(Today()), 1, 0 ) as IsCompletedMonth

and then use this in your Set Analysis expression:

Sum({\$<IsCompletedMonth={1}>} Sales)

Just make sure that you don't show "empty" months.

HIC