set analysis with if statement

[userid128223]

Below statement gives me current total sales of all the companies despite any selection.

What I want to do is:

Give me total sales of currently selected company, excluding other companies, excluding any time line selection.

=if(PurchaseDate > AddMonths(today(),-12)   this is true then  sum(totalsales) - (total cost)   excluding time period selection.

How can i do that.

Thx

[#2]

should be something like this...

sum(  {$<PurchaseDate ={'>$(=AddMonths(today(),-12) '}>} totalsales) -
sum({$<PurchaseDate ={'>$(=AddMonths(today(),-12) '}>} total cost)

if you are dealing with period, it's better to build a calendar master data, says from past 2 year until next 50 years.

with columns like MMM YYYY, Quarter, row numbering, YYYYMM in digit (simply YYYY*100 + MM)

row numbering will be very very useful and handly when u want to do past 12 month until prev month... etc....

[userid128223]

your suggestion did not work.

Any other help.

[#4 · Accepted solution]

Hi,

Suppose the date format for PurchaseDate is DD-MM-YYYY then

Create a variable vDate= date(AddMonths(today(),-12),'DD-MM-YYYY')

=sum( {<PurchaseDate ={">=$(vDate)",Year,Month}>} totalsales) -

sum( {<PurchaseDate ={">=$(vDate)",Year,Month}>} total cost)

Note- to exclude time period selections bypass Year and Month as i did in the expression.

Regards

Apurva