16 Replies Latest reply: Jul 2, 2015 10:53 AM by Srikanth P

# Hide dimensions who are used in acc sum

Hi there,

I used the folowing expression to accumulate a expression 12 yeamonth values back

=rangesum(above( sum({<CalenderYearMonth={"<=\$(=max(Year)&right(0&max(Month),2))"}>}#SalesInvoiceQuantityInKilo),0,12) )

Is it possible to hide the first 12 dimension values.

The set analysis is used to select my calendar with is not connected becasue i have a fiscal and a normal calendar.

Who can help me with this question?

Regards,

Dirk

• ###### Re: Hide dimensions who are used in acc sum

Please explain the question with sample data & qvw file

• ###### Re: Hide dimensions who are used in acc sum

Not sure if I completely understand what you want, but may be try this and see if this is what you intended for?

=If(Sum(#SalesInvoiceQuantityInKilo) > 0, RangeSum(Above(Sum({<CalenderYearMonth={"<=\$(=max(Year)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo),0,12)))

• ###### Re: Hide dimensions who are used in acc sum

Because it a acc sum the first 12 values are above 0 but not totaly acc for 12 months.

I only want to see te months who can look 12 months back

• ###### Re: Hide dimensions who are used in acc sum

Try this:

=If(RowNo() <= 12, RangeSum(Above(Sum({<CalenderYearMonth={"<=\$(=max(Year)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo),0,12)))

• ###### Re: Hide dimensions who are used in acc sum

That doesn't work because it still shows the dimms who are used for te calculation

• ###### Re: Hide dimensions who are used in acc sum

=If(Sum({<CalenderYearMonth={">\$(=(max(Year)-1)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo) > 0, RangeSum(Above(Sum({<CalenderYearMonth={"<=\$(=max(Year)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo),0,12)))

Note: =(max(Year)-1)&Right(0&max(Month),2) in a text box object should give you the date from which (not including) you want to see in your dimension value in the chart.

HTH

Best,

Sunny

• ###### Re: Hide dimensions who are used in acc sum

This formula gives the same output as the previous

• ###### Re: Hide dimensions who are used in acc sum

Would you be able to share a sample?

• ###### Re: Hide dimensions who are used in acc sum

Here is a example and a picture what i want

• ###### Re: Hide dimensions who are used in acc sum

Same idea as I used in my example, only issue is that your YearMonth is a text string and not date. I recommend converting YearMonth to a date field in the script, may be using MonthName() function. See if this helps:

• ###### Re: Hide dimensions who are used in acc sum

See attached example.

• ###### Re: Hide dimensions who are used in acc sum

BTW, If you want Hide total dimension values, go to Chart Properties --> Presentation --> Select Dimension --> Select the Hide

• ###### Re: Hide dimensions who are used in acc sum

This is an example of te problem.

The first 12 months are used to accumalate the 13 value and i want only to see the 13 value and higher

• ###### Re: Hide dimensions who are used in acc sum

Attaching a sample for you. May be this will explain what I am trying to do:

Expression 1: =RangeSum(Above(Sum({<Date = {"\$(='<=' & Date(Today(), 'M/DD/YYYY'))"}>}Value), 0, 12))

This one is same as yours.

Expression 2: Sum({<Date = {"\$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY'))"}>}Value)

Forced 0 for Months we don't want to see on the line chart

Expression 3:

=If(Sum({<Date = {"\$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY'))"}>}Value) > 0,

RangeSum(Above(Sum({<Date = {"\$(='<=' & Date(Today(), 'M/DD/YYYY'))"}>}Value), 0, 12)))

This is what you need.

Using the third expression in the line chart, I get this:

I hope this will help.

Best,

Sunny

• ###### Re: Hide dimensions who are used in acc sum

What id we have more than 2 years of data ?