Expression help

[Anonymous]

Hi All,

i want to add some bucketing in my expression ..

This is my expression

SUM({$<FinancialYear=,Quarter=,MonthName=,[Date] ={"<=$(=MonthEnd(Max([Date])))"}>}[Sale])

And I want to add below condition in above expression

=if((num((num(vDateForAgeing)-[Post Date ]),'#######0') )<=180 ,'Below Six Month',

if(num((num(vDateForAgeing)-[Post Date ]),'#######0')>=180.1,'Above 181',

0))

Thanks

[senpradip007]

Could you explain vDateForAgeing ?

[Anonymous]

vDateForAgeing

=IF(ISNULL(GetFieldSelections(MonthName))=0 OR ISNULL(GetFieldSelections(FinancialYear))=0,DATE(MonthEnd(MAX(Date))),DATE(Today()))

[Anonymous]

What exactly you want to achieve by adding this condition in your expression?

[jagan]

Hi,

Try this

=if((num((num(vDateForAgeing)-[Post Date ]),'#######0') )<=180  AND num((num(vDateForAgeing)-[Post Date ]),'#######0')>=180.1,

SUM({$<FinancialYear=,Quarter=,MonthName=,[Date] ={"<=$(=MonthEnd(Max([Date])))"}>}[Sale]))

Regards,

Jagan.

[jonathandienst]

If vDateForAgeing and Post Date are numeric QV date values, then this is enough:

...

If(vDateForAgeing - [Post Date ] <= 180, 'Below Six Month',

  If(vDateForAgeing - [Post Date ] >=180.1, 'Above 181', 0)) As Buckets,

...

If there are more buckets, you might want to consider interval matching rather than a nested if. Does [Post Date ] really have a trailing space in the field name?

[Anonymous]

Hi Jagan,

I need to create two separate column for this .

Thanks

[Anil_Babu_Samineni]

Perhaps This

=if(num(num(vDateForAgeing)-[Post Date]),'#######0') <= Num(180),'Below Six Month', num((num(vDateForAgeing)-[Post Date ]),'#######0')>=180.1,'Above 180.1', SUM({$<FinancialYear=,Quarter=,MonthName=,[Date] ={"<=$(=MonthEnd(Max([Date])))"}>}[Sale]))

If you use this, What you are getting in your application, Would you provide sample application which demonstrates the o/p. If this gives number let me know. Better approach is want to see.

LET vDateForAgeing = IF(ISNULL(GetFieldSelections(MonthName))=0 OR ISNULL(GetFieldSelections(FinancialYear))=0,DATE(MonthEnd(MAX(Date))),DATE(Today()))

[miskin_m]

Hi Vijay,

You need to add your aging expression as a calculated dimension to create two columns under dimension tab.

Hope this is what you want.

[Anonymous]

Hi Vijay,

In your expression, have you tried to floor the expression. Something like:

=if((num((num(floor(vDateForAgeing))-[Post Date ]),'#######0') )<=180 ,'Below Six Month',

if(num((num(floor(vDateForAgeing)-[Post Date ])),'#######0')>=180.1,'Above 181',

0))