Problem with SetAnalysis - Quarter

[#1]

I need a help for a problem with Set Analysis.

This is my problem:

I would like to build a Pivot table with the Set Analysis where in rows I have the location and in the columns

I have sold in the last quarter, the quarter before last in the second column, and so on.

My idea, as you can see from table 3 of the attached file, is to consider

the maximum value of the data then take the first day of the month and build me a value similar

to the variable "QuarterYear" script loading:

  Sum ({<QuarterYear = {"$ (= (Year (monthstart (max (SoldDate), 0)) & '/' & Ceil (Month (monthstart (max (SoldDate), 0)) / 3)))"}> Sold})

For the second quarter of the previous column I would use a formula like this:

Sum ({<QuarterYear = {"$ (= (Year (monthstart (max (SoldDate) - 3)) & '/' & Ceil (Month (monthstart (max (SoldDate) - 3)) / 3)))" }>} Sold)

where to find the previous quarter subtract three months from the current month.

Can anyone help me?

Thank All

[rubenmarin · Accepted solution]

Hi Marino, I removed the extra espaces in set analysis and it worked.

From previous months you'll need tho put the '-3' outside the monthstart function, so it substract from month, not from max(date)

[maxgro]

what about a pivot with ?

dimensions

Location

QuarterYear

and expression

Sum(   {$ <SoldDate={">=$(=AddMonths(MonthStart(max(SoldDate)),-11))"}> }  Sold)

change -11 with the number of months you need

[#4]

Thank Ruben.

Very Nice.

But I do not understand what is the difference with my solution.

Please you tell me the differences?

many thanks

[rubenmarin]

You had a blank space between '$' and '(', also before closing the double quotes was another empty space:

Sum(  {<QuarterYear={"$(=(Year(monthstart(max(SoldDate),0)) &'/' &Ceil(Month(monthstart(max(SoldDate),0))/3)))"}> }  Sold)

Removing those it worked.

Glad to help.