Date format

[krishna20]

Hi Friends,

I'm having problem in date format.

=sum({$<TP_MONTH={$(=MAX(TP_MONTH)))-12)},TP_YEAR=,TP_MONTH=>}TP_GROSS)

I'm having in datamodelling TP_MONTH as JAN,FEB,MARCH,APR.....

How can i take in set analysis as 1,2,3..........

Can any one pls provide sample one..

Regards

Krishna

[krishna20]

Hi,

Actually my requirement is to calculate (LYTD) sum( same the month of last year AMOUNT).

I'm having TP_Month format like JAN,FEB,...

I'm not getting correct result when i used the above expression

Please guide me how to calculate with the given requrement.

Regards

Krishna

[Anonymous]

Hi,

First of all, you have to convert TP_MONTH in date format in backend,

Date(Date#(TP_YEAR&TP_MONTH, 'YYYYMMM')) as TP_DATE,

Month(Date#(TP_YEAR&TP_MONTH, 'YYYYMMM')) as TP_MONTH

And then for last year YTD you may try below,

=Sum ({$<TP_YEAR={"$(=Max(TP_YEAR)-1)"}, TP_MONTH={"<=$(=Max(TP_MONTH))"}>} TP_GROSS)

PS: There might be a typo error in the script.

[#4]

Do you have a field in date format?

if you have it try something like this:

Sum ({$<TP_DATE={"(>=$(=date(yearstart(addyears(Max(TP_DATE),-1)))) <=$(=date(addyears(Max(TP_DATE),-1))) "}>} TP_GROSS)

not tested maybe some ")" missing

[krishna20]

Hi Satya,

Even i changed in the script ,i'm getting the same result.

what may be the problem?

Regards

Krishna

[Anonymous]

Hi,

Share some sample data with app for quick resolution.

[krishna20]

Hi Jayadev,

i have attached the file here.which not modified.Can u pls tell whether the LYTD which i done is right ..

Regards

krishna

[Anonymous]

Hi Kalyan,

I quickly checked your logic for LYTD Premium and it seems to be fine. Even the result you are getting is correct. As per below, if currency=3 and premium=2 than sum of previous year TP_NetPremium_LC_3 which is 83132.15. And this is what you are getting in your dashboard.

[krishna20]

Hi Satya,

I'm getting previous year figure is right.But i need to show the data for previous year for same month.i.e; july.

can u please modify it.

Regards

krishna

[#10 · Accepted solution]

It's not correct, you are not doing a LYTD you're filtering the whole last year

I should use TP_APPR_DT field is a date but un number format:

LYTD formula:

=if(

    vOptionCurrency = 1 and vOptionPremium = 1, Sum({<TP_APPR_DT={">=$(=num(yearstart(addyears(Max(TP_APPR_DT),-1)))) <=$(=num(addyears(Max(TP_APPR_DT),-1))) "}>}TP_NetPremium_LC_1),

     if(

    vOptionCurrency = 1 and vOptionPremium = 2, Sum({<TP_APPR_DT={">=$(=num(yearstart(addyears(Max(TP_APPR_DT),-1)))) <=$(=num(addyears(Max(TP_APPR_DT),-1))) "}>}TP_GrossPremium_LC_1),

    if(

    vOptionCurrency = 3 and vOptionPremium = 1, Sum({<TP_APPR_DT={">=$(=num(yearstart(addyears(Max(TP_APPR_DT),-1)))) <=$(=num(addyears(Max(TP_APPR_DT),-1))) "}>}TP_NetPremium_LC_3),

    if(

    vOptionCurrency = 3 and vOptionPremium = 2, Sum({<TP_APPR_DT={">=$(=num(yearstart(addyears(Max(TP_APPR_DT),-1)))) <=$(=num(addyears(Max(TP_APPR_DT),-1))) "}>}TP_GrossPremium_LC_3)))))

[krishna20]

Hi David,

But u didn't compared the month date here.sorry,i'm new this concept.

Regards,

Krishna