Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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(QuarterStart(=max({}Year)-1))) <=\$(=date(QuarterEnd(Today(),-4)))"}>}[Nett Amount new EUR])

Any ideas?

Cheers,

Mike

1 Solution

Accepted Solutions
MVP

May be try this

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

7 Replies
MVP

What is your chart dimension where you are trying this?

Anonymous
Not applicable
Author

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..

MVP

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()))

MVP

May be try this

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

Anonymous
Not applicable
Author

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))"}>}[Nett Amount new EUR]) / Sum({1=\$(=Date(YearStart(Today(), -1)))<\$(=Date(QuarterStart(AddYears(Today(), -1), 1)))"}>}[Nett Amount new EUR])
Anonymous
Not applicable
Author

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

MVP

Super