last year and last month to one expression

[sagaraperera]

Dear All

I have given below QVD file and using Trandatecalender:

CrossTable(DESCRIPTION, Actual, 7)

LOAD BCOD,

     MONTH,

     YEAR,

     MONM,

     DATE(Floor(MonthStart(MakeDate(YEAR,MONTH)))) as TRAN_DATE,

      MONTH2,

     CLA_CODE,

     GROSS_PREMIUM,

     SRCC_PREMIUM,

     REINSURANCE_FUND,

     FOREIGN_COMPANIES,

     CEEDED_PREMIUM,

     NRP,

     UPR_CHARGE,

     NEP,

     SRCC_TC_COMMISSION,

     FOREIGN_CO_COMMISSION,

     COMMISSION_INCOME,

     NET_REVENUE,

     GROSS_CLAIMS,

     CLAIMS_RES_FUND,

     EXGRATIA_CLIMS_RES_FUND,

     CLAIMS_PROCESSING_EXPENSES,

     LEGAL_FEES,

     RI_RECOVERIES_RI_FUND,

     RI_RECOVERIES_SRCC_TC,

     RI_RECOVERIES_FOREIGN,

     SALVAGE_RECOVERIES,

     NET_CLAIMS,

     COMMISSION_EXPENSES,

     PRODUCTION_BONUS,

     DAC_CHARGE,

     ACQUISITION_COST,

     GROSS_PROFIT,

     STAFF_EXPENSES,

     ADMIN_EXPENSES,

     SELLING_EXPENSES,

     FINANCIAL_EXPENSES,

     DEPRICIATION_EXPENSES,

     MANAGEMENT_EXPENSES,

     INV_INCOME_EXT,

     INV_INCOME_INT,

     OTHER_INCOME_EXT,

     OTHER_INCOME_INT,

     INV_OTHER_INCOME,

     NET_PROFIT

Trandatecalender:

LOAD

DAY(TRAN_DATE) AS TRAN_DAY,

DATE(TRAN_DATE) AS TRAN_DATE,

DATE(monthstart(TRAN_DATE), 'MM-YYYY') as MONTH_YEAR,

WEEK(TRAN_DATE) AS TRAN_WEEK,

YEAR(TRAN_DATE) AS TRAN_YEAR,

MONTH(TRAN_DATE) AS TRAN_MONTH,

MONTH(TRAN_DATE) &'-'& WEEK(TRAN_DATE) AS MONTH_WEEK;

LOAD DATE (TRAN_MINDATE + ITERNO() -1) AS TRAN_DATE WHILE (TRAN_MINDATE + ITERNO () -1) <= NUM (TRAN_MAXDATE);

LOAD

MIN(TRAN_DATE) AS TRAN_MINDATE,

MAX(TRAN_DATE) AS TRAN_MAXDATE

RESIDENT PL;

That is example -

I selected Year 2016 and Month of January in my field and i want get 2015 February value ( In this script want a depending on a select month year)

i using given below script get a last year value

=sum( {$<TRAN_YEAR= {"$(=Only([TRAN_YEAR]-1))"},DESCRIPTION= {'NRP'} >} [Actual])

is there any possibility to get the last year and last and

sagara

[ramasaisaksoft]

Solution 1 – Set Analysis

Pre-requisites:

• Date breakdown in script
  • Year(OrderDate) as OrderYear,
  • DayNumberOfYear(OrderDate) as OrderDayNumberOfYear,
• Variables for current date in script (assuming being loaded each day)
  • let varThisYear = year(today());
  • let varLastYear = (year(today())-1);
  • let varDayNumberOfYear = DayNumberOfYear(today());

Expression to calculate sales for last year up to corresponding day last year:=Sum ({1< OrderYear = {$(varLastYear)}, OrderDayNumberOfYear= {“<=$(varDayNumberOfYear)”}>}OrderSalesAmount)This is not an easy calculation to read so an alternative method can be deployed as per Solution 2.

Solution 2 – LYTD Flag

Pre-requisite

• Flag in script to identify records from last year corresponding to today’s date
  • if(year(OrderDate)=(year(today())-1),if(DayNumberOfYear(OrderDate)<=DayNumberOfYear(today()), 1,0)) as LYTD_Flag,

Expression to calculate sales for last year up to corresponding day last year:

=Sum ({1<LYTD_Flag = {1}>}OrderSalesAmount)