How to sum values of recent quarter per table row

[#1]

Hello,

I have a straight table with two expressions to give me the sum of amount for each recent quarter per user. max(quarter) provides the correct recent quarter per row but using max() within the set analysis the total recent quarter of all rows is used.

standard expression:

if(Quarter = max(Quarter), sum(Amount), 0)

expression with set analysis:

sum({$ <QuarterStartDate = {"$(=date(max(QuarterStartDate)))"}> } Amount)

Both expressions don't work.

Can someone see what i did wrong?

Invoice:
load * inline [
    User, Amount, Date
    A, 25, 2013-05-20
    A, 30, 2013-06-23
    A, 35, 2013-07-26
    B, 20, 2013-09-20
    B, 50, 2013-10-23
    B, 80, 2013-12-12
    C, 10, 2013-12-28
    C, 20, 2014-01-10
    C, 40, 2014-01-12
];

Calendar:
load
    Date,
    Dual('Q' & Ceil(Month(Date)/3) & '/' & Year(Date), QuarterStart(Date(Date))) as Quarter,
    QuarterStart(Date(Date)) as QuarterStartDate
resident Invoice;

[Anonymous]

Erik,

The evaluation of the set takes place only once for the chart so the max quarter in your example would always be the highest quarter in your data regardless of the users.

You could create a separate table in your data model where you group those amounts by user into their most recent quarter?  I haven't figured out the syntax for that scripting, and their might be a front-end way to accomplish(?) but that is the road I would pursue.

Not much help probably(?)

[jerem1234]

Please find attached. Wound up using firstsortedvalue. The expression was:

FirstSortedValue(distinct aggr(sum(Amount), User, QuarterStartDate), -QuarterStartDate)

It will show up as wrong, but it is correct and will give correct value.

PFA

Hope this helps!

[Anonymous]

Didn't think of that one.  Obvious!

[#5 · Accepted solution]

Hey Erik,

If you want to use IF on front end use this formula:

sum(if(aggr(NODISTINCT max(QuarterStartDate),User)=QuarterStartDate,Amount))

Hope it helps

Thanks

AJ

[#6]

Thank you both!

@jerem1234: I've never heard of this function, but it works. There are no errors in formula editor of QV9.

@Ajay: In the end I have chosen your solution.