Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Kalyan,
See attachment. In Sheet38, I did some testing and looks like the LYTD logic is fine. I have taken simple sum of values and LYTD logic and both columns are matching for 2013.
Let me know if I am missing something.
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.
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)
Hi Yashwin,
CLM_DATE is date filed in the format DD/MM/YYYY.
Regards
Krishna
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
Hi Kalyan Krishna,
Try removing "<=" and take CLM_SETL_DTMonth as suggested by satyadev...
regardz,
Vaibz..
Hi,
I did as u suggested.I'm getting zero.
Regards
Krishna
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.
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
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.