Set Analysis Help

[#1]

=Sum({$<Quarter={'Q4'},{Book1})> - sum({$<Quarter={'Q3'},{Book1} >} grossamount * 4)

Hi All,

Can someone please guide me towards the correct way of doing this SET expression. I have created a Bookmark called Book1 for the accounts needed for revenue calculation. I want to find the difference of income between the two quarters and times it by 4.

Requirements: Difference of income (book1 represents income accounts) between Quarter 4 and Quarter 3 times it by four.

Please shade some light.

Thanks in advance,

[sunny_talwar]

May be this:

=(Sum({[Book1]<Quarter={'Q4'}>} grossamount) - Sum({[Book1]<Quarter={'Q3'}>} grossamount)) * 4

[#3]

Hi Sunny,

The formula works great, thanks. The formula is stagnant and solid and doesn't change if I make selections. Is there anyone I can automate this formula and make it dynamic so whenever the end user clicks on a year and the quarter the formula changes.

I want to have this:

=money(Sum({[Book1]<Quarter={'Selected Quarter'}>} grossamount) - Sum({[Book1]<Quarter={'Previous Quarter}>} grossamount)) * 4

I have also tried converting the textbox to money but isn't working.

Thanks in Advance,

[sunny_talwar]

Not 100% sure, but try this:

=money(Sum({[Book1]<Quarter={'Selected Quarter'}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount) - Sum({[Book1]<Quarter={'Previous Quarter}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount)) * 4

[#5]

Hi Sunny,

I know it's difficult to get the formula right without having the data to practice with, I thank you for your hardwork. This formula might be it but the 'Selected Quarter' and "Previous Quarter' aren't actual fields in my dataset. Selected Quarter can be any quarter chosen by the end user after he/she have chosen the year.

Example: Let's say the user selects 2014 and Third Quarter, the formula should look like this.

=money(Sum({[Book1]<Quarter={'Q3'}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount) - Sum({[Book1]<Quarter={'Q2'}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount)) * 4

Once again, thanks Sunny!!!!!!!!!

[sunny_talwar]

Create another field in the script like this:

LOAD Ceil(Month(Date)/3) as QuarterNum

and then try this expression:

=Money(Sum({[Book1]<QuarterNum={"$(=Max({$}QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount) - Sum({[Book1]<QuarterNum={"$(=Max({$}QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount)) * 4

[#7]

Sunny I already have a Quarter field called the DimQuarter and DimYear for Year field. This formula can actually do it but for some odd reason it gives me a zero for all the filters, just zero even though the formula has no mistake whatsoever. I wish I can send you this file but I have my hands shackled.

[sunny_talwar]

Well the Quarter you have is a text and the one I mentioned above is going to be a number. You cannot find Max(Quarter), but you can find Max(QuarterNum).

LOAD Ceil(Month(Date)/3) as QuarterNum

This will give you 1, 2, 3, 4 instead of Q1, Q2, Q3, Q4

[#9]

Awesome Sunny it seems to be working. Still I can't get the formula to display the dollar sign. And also I want to divide the whole formula now by the expenses I have outlined the expense accounts in my Bookmark 2 called Book 2 for the previous quarter so would my expression at the bottom work in this case.

=Money(Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.expenses)

[jagan]

Hi,

Try this expression.

=Money((Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.expenses))

Regards,

jagan.