Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Please explain the question with sample data & qvw file
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)))
BTW, If you want Hide total dimension values, go to Chart Properties --> Presentation --> Select Dimension --> Select the Hide
Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations
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
Try this:
=If(RowNo() <= 12, RangeSum(Above(Sum({<CalenderYearMonth={"<=$(=max(Year)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo),0,12)))
That doesn't work because it still shows the dimms who are used for te calculation
What about this
=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
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