7 Replies Latest reply: Apr 4, 2017 9:37 AM by Sunny Talwar

# Cumulative expression quarters

Hi guys,

I want to show our sales in the current year vs the cumulative sales of the quarters last year. At this period of the year, this means that I need to show the cumulative sales of Q1 and Q2. I want a function that automatically shows the the cumulative sales of Q1, Q2 and Q3 when Q3 has started.

I tried the following script, but that did not work as I hoped for:

SUM({1<Date ={">=\$(=date(QuarterStart(=max({<year>}Year)-1))) <=\$(=date(QuarterEnd(Today(),-4)))"}>}[Nett Amount new EUR])

Any ideas?

Cheers,

Mike

• ###### Re: Cumulative expression quarters

What is your chart dimension where you are trying this?

• ###### Re: Cumulative expression quarters

I'm using a few KPI object to show the following measures:

CYTD vs LYTD (which is working)

CYTD vs Last years Quarters (in this case Q1 and Q2) - Not working

CYTD vs Target (which is working)

Edit*

In the KPI object it's not possible to use a dimension..

• ###### Re: Cumulative expression quarters

May be try this

Sum({1<Date = {">=\$(=Date(YearStart(Today(), -1)))<\$(=Date(QuarterStart(AddYears(Today(), -1), 1)))"}>}[Nett Amount new EUR])

• ###### Re: Cumulative expression quarters

Thanks a lot Sunny, you're expression helped me!

I used the following expression to get a percentage of CYTD vs Last year quarters (the quarters that already have passed and the quarter we're currently in).

sum({1< Year={"\$(=max({<year>}Year))"}>}[Nett Amount new EUR])

/

Sum({1<Date = {">=\$(=Date(YearStart(Today(), -1)))<\$(=Date(QuarterStart(AddYears(Today(), -1), 1)))"}>}[Nett Amount new EUR])

• ###### Re: Cumulative expression quarters

Super

• ###### Re: Cumulative expression quarters

Suppose You have expression:

Sum({<Date=,Year=,Year={"\$(=max(Year))"} , Date={"<= max(Date)"}>} Sales) : the sum of sales for the max  selected (year) till the max selected date (or existing date, if no selection has been made).

You'll only need to put as a dimension:

Quarter:

And alter your expression as follow:

RangeSum(Above(Sum({<Date=,Year=,Year={"\$(=max(Year))"} ,  Date={"<= max(Date)"}>} Sales),0,rowno()))

And add a second one for the previous year:

RangeSum(Above(Sum({<Date=,Year=,Year={"\$(=max(Year)-1)"} , Date={"<= max(Date)"}>} Sales),0,rowno()))

Per analogy:

Dim: Month / Measure: Sum(Sales)

vs

Dim: Month / Measure: RangeSum(Above(Sum(Sales),0,rowno()))

• ###### Re: Cumulative expression quarters

Thanks for your reply Omar, the problem in this case was that it was not possible to add a dimension in a KPI object.