LYTD

[krishna20]

Hi Friends,

i'm facing problem in LYTD.in my expression last year part  is working but the month part is not working.

CLM_DATE format DD/MM/YYYY

CLM_SETL_DTYear format is 2014,2013,2012.....

CLM_SETL_DTMonth format is JAN,FEB,MAR,,.....

can anyone pls help me out.

=

   if(

   vOptionCurrency = 1, (Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE ={"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {1}>}CS_AMT_LC_1) //Payment

                         -(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {3}>}CS_AMT_LC_1)))  //Recovery

                            -(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {2}>}CS_AMT_LC_1)  //Reversal Of Payment

                              -(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {4}>}CS_AMT_LC_1))),  //Reversal of Recovery

    if(

    vOptionCurrency = 3, (Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {1}>}CS_AMT_LC_3) //Payment

    -(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {3}>}CS_AMT_LC_3)))  //Recovery

       -(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {2}>}CS_AMT_LC_3)  //Reversal Of Payment

          -(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE = {"=num(month(addmonths(Max(CLM_DATE),-12)))"},CS_EST_TYPE = {4}>}CS_AMT_LC_3)))) ) //Reversal of Recovery

Regards

Krishna

[#2]

Hi,

As i saw your expression, want to know CLM_DATE is it a DATE or MONTH Field. If its date , then as per your expression you are passing month which might be the reason its not working.

If its a month try >= instead of =  in set analysis.

[Anonymous]

Hi,

Your month comparison looks weird to me. CLM_DATE is in DD/MM/YYYY format and you are comparing with Num(Month(Addmonths(Max(CLM_DATE),-12))) which is in MM format.

You may either replace CLM_DATE with CLM_SETL_DTMonth in set analysis comparison (see below)

Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)}, CLM_SETL_DTMonth={"<=$(=Month(AddMonths(Max(CLM_DATE),-12)))"}, CS_EST_TYPE = {1}>}CS_AMT_LC_1)

Or use Today function to compare month (see below)

Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)}, CLM_DATE ={"<=$(=Date(AddMonths(Today(),-12)))"}, CS_EST_TYPE = {1}>} CS_AMT_LC_1)

[krishna20]

Hi Yashwin,

CLM_DATE is date filed in the format DD/MM/YYYY.

Regards

Krishna

[krishna20]

Hi satyadev,

By using this expression i'm reaching the database value.But not exact value. I tried which you suggested me,but value differs much.

(Sum({<CLM_SETL_DTYear = {$(=Year(Today())-1)},CLM_DATE ={"(<=$(=Date(AddMonths(max(CLM_DATE),-12),'DD/MM/YYYY')))"},CS_EST_TYPE = {1}>}CS_AMT_LC_1)

My value 29,454,249 .08

DB Value 29,451,049.08

Shall i expect any other solution for this.

Regards

Krishna

[Anonymous]

Hi Kalyan Krishna,

Try removing  "<="  and take CLM_SETL_DTMonth as suggested by satyadev...

regardz,

Vaibz..

[krishna20]

Hi,

I did as u suggested.I'm getting zero.

Regards

Krishna

[Anonymous]

So basically there is data difference of 3200 between your value and DB value. Can you do simple testing and see where is the problem. Create straight table with CLM_DATE in dimension and above script in expression. Also add another script Sum(CS_AMT_LC_1) and see where is the gap.

[krishna20]

Hi Satyadev,

My expression is calculation of Payment,Reversal of Payment,Recovery, and Reversal of Recovery.How can i compare total with this expression amount.

Regards

Krishna

[Anonymous]

Krishna,

Just test Payment data first with my earlier suggestion. If you can figure out this then others can be resolved easily. If possible share your apps with limited and scrambled data to look into exact issue.