Set Analysis and Aggr for Sales Data

[MassicotPSCU]

Hi,

I have sales data table, where records are added for the dollar amount of sales for each representative by year.  In that table I also have a sales ID field and a field that can determine if the year in the year field is the current year.

I'm trying to write an expression to get the sum of sales for each representative by sales ID, for the current year only. While at the same time ignoring any selection on the year field.

This is step 1 where I'm using set analysis to only bring back the sales for the current year and to ignore any selection on the year field. It works perfect aside from the fact that it brings back the wrong number. This is why I want to aggregrate by Sales ID which brings back the right number. I have tried using the distinct function but that also brings back a wrong number.

sum({$<CurrentYear={"1"},Year=>}sales)

This is step 2 where I'm using the aggr function to add each sales based on the distinct sales ID.

sum({$<CurrentYear={"1"},Year=>}aggr(sales,[Sales ID]))

The problem with step 2 is that the year field selection is not ignored. I don't know if it's because i'm placing the aggr in the wrong spot. For some reason it doesn't seem to work because when I make a selection on the year dimension, I get back a value of zero. The value for the Current Year should be the same, even if another year is selected in say a filter pane.

Here's my sample data.

[PedroNetto]

Hi @MassicotPSCU ! What result you expected? 

[MassicotPSCU]

Hi @PedroNetto , I was expecting to get back the correct sum of sales (which I did) for each representative for the current year. I also wanted to ignore any selection on the year field. So, while I do get back the right number, anytime I make a selection on the year field, the value changes which is not supposed to happen.  I'm basically looking for a way to ignore that year field if one of its values is selected in my filter pane.  That is why I tried that " Year=>}" in my expression for Step 2.

[ckarras22 · Accepted solution]

Hello,

Can you try ?

sum({$<CurrentYear={"1"},Year=>}aggr(sum({$<CurrentYear={"1"},Year=>}sales),[Sales ID]))

[MassicotPSCU]

That worked! I don't know why I didn't try putting the set analysis on both sides haha. Thank you @ckarras22 !