Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Could you explain vDateForAgeing ?
vDateForAgeing
=IF(ISNULL(GetFieldSelections(MonthName))=0 OR ISNULL(GetFieldSelections(FinancialYear))=0,DATE(MonthEnd(MAX(Date))),DATE(Today()))
What exactly you want to achieve by adding this condition in your expression?
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.
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?
Hi Jagan,
I need to create two separate column for this .
Thanks
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()))
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.
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))