Set Analysis Syntax Problem - Previous Quarter

[Dayna]

Hello All!

I'm trying to get the previous quarter, and I think my syntax is slightly out... Here's what I have:

=sum({$<Applicator_Date={"$(=date(addmonths((Applicator_Date),-3)))"}>}Applicator_Cost)

Applicator_Date is a date field. Any ideas? 

If I do this in a text box and change it to:

=date(addmonths(max(Applicator_Date),-3)) then I get the result I'd expect for the max date in the calendar, so I'm not sure what I'm missing....

Thanks!

[chrismarlow]

Hi,

The set expression is calculated once for the object, i.e. if that is a table is the same for every row.

If you want to show a date, current month and prior quarter (meaning 3 months ago, or meaning accounting quarter end, not sure I understand your data) you could use something like ABOVE function with a suitable offset;

Cheers,

Chris.

[yassinemhadhbi]

Good morning

The best idea is , to create quarter Field in the script  'Q' & Ceil(Month([Order Date])/3) As Quarter,

Then create Another column that Has : autonumber('Q' & Ceil(Month([Order Date])/3)) as QuarterNum

Then in the set analysis you will just do 

sum({<QuarterNum={'$(=max(QuarterNum)-1)'}>}Applicator_Cost)

[Denisg]

Hi,

In case you don't want to have a calendar with a Quarter field as described by Yassinemhadhbi , you can create a dummy date, 3month after. 

[Dayna]

Hi All,

The above isn't bringing back the results I'd expect, here's an example with the new calculation as per this thread:

Here's what I would expect...

Hopefully the above makes sense to what I'm trying to achieve? 

[Denisg]

Hi,

Above(Value) as the expression of the « Expected Value » column should do the trick.

[Dayna]

Hi @Denisg ,

How would you do that in a pivot table where the dimension is on the left hand side, but the quarter timescale at the top? That's where I'd imagine the set analysis one would only look at the previous quarter for the item in question.

Thanks,

[Denisg]

Hi,

Not sure I understand.

can you please design that table like you did above?

[Dayna]

Yes of course, I've achieved the below so far using PEEK() within the load script, but at the time I thought the Set Analysis approach would be 'safer':

So this is a pivot, with the quarter as the dimension at the top, whereas the middle expression should be the value for the previous quarter. This is working using peek in the load script as per:

if(Applicator_ID=peek(Applicator_ID) , peek(Applicator_Cost),null()) as Applicator_PreviousQuarter

However, I was hoping to get the set analysis approach working too! 

[chrismarlow]

Hi,

There are functions Before and After that work in Pivot Table like Above and Below work in Straight Table.

Cheers,

Chris.