Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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 ={">=$(=date(QuarterStart(=max({<year>}Year)-1))) <=$(=date(QuarterEnd(Today(),-4)))"}>}[Nett Amount new EUR])

Any ideas?


Cheers,

Mike

1 Solution

Accepted Solutions
sunny_talwar

May be try this

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

View solution in original post

7 Replies
sunny_talwar

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

OmarBenSalem

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

Capture.PNG




sunny_talwar

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>}Year))"}>}[Nett Amount new EUR])

/

Sum({1<Date = {">=$(=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.

Still a helpfull reply!:)

sunny_talwar

Super