My SET expression on work for year 2016 , How to get 404k for dec 20125 ? Now display 0

[paulyeo11]

Hi All

I have below expression work fine for April 2016 current month sales :-

=num(sum(

{

$

<

year = {$(=max(year))},

month =

{

"<=$(=max(

{

<year={$(=max(year))}>

}

month)-0)>$(=max(

{

<

year={$(=max(year))

}

>

} month)-1)"

}

>

}

sales), '$#,##0;($#,##0)')

My issue is when i change my current month = march 2016 , may i know how to make Dec 2015 display as 404K ?

Paul

[paulyeo11]

my qv doc

[sunny_talwar · Accepted solution]

Try this expression:

=Num(Sum({$<date = {"$(='>=' & Date(MonthStart(Max(date), -3), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(date), -3), 'DD/MM/YYYY'))"}, year, month>} sales), '$#,##0;($#,##0)')

I would also change the all other months to be driven based on date instead of month and year because month and year complicate things when you are moving from one year to another

[paulyeo11]

Hi Sunny

Thank you very much , I just manage to convert sales to rEVENUE by refer to old post. i think now my SET analysis have improve a bit , thank you for your encouragement.

=Num(Sum({$

<

date = {"$(='>=' & Date(MonthStart(Max(date), -1), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(date), -1), 'DD/MM/YYYY'))"},

year,

month,

rEVENUE={"rEVENUE"}

}

>

}Amount*1), '$#,##0;($#,##0)')

[sunny_talwar]

Awesome, I am glad you are making attempts to make improvements at your end