N-1 set analysis

[squalitooo]

Hello,

I want to calculate this formula for N-1.

Sum({<Year=, Quarter=, Month=, Week=, Date_Facture=, Date_Facture={">=$(=(YearStart(Max(Date_Calendar)))) <=$(=today(today(Date_Calendar),))"}, Type_Facture = {'FACTURES'}, Site_Facture = {'XXXX'}, Code_Client-={'CLIENTSX'}>}Montant_HT_Ligne_Facture)

I have test this but no working:

Sum({<Year=, Quarter=, Month=, Week=, Day=, Date_Facture=, Date_Facture={">=$(=(YearStart(Max(Date_Calendar),-1))) <=$(=dayend(today(Date_Calendar),-365))"}, Type_Facture = {'FACTURES'}, Site_Facture = {'XXXX'}, Code_Client-={'CLIENTSX'}>}Montant_HT_Ligne_Facture)

Anyone have an idea for calculate YTD N-1 (day is today N-1)

[HugoRomeira_PT]

Hello,

Try the following expression:

Sum({<Year=, Quarter=, Month=, Week=, Date_Facture=,
Date_Facture={">=$(=(Addmonths(YearStart(Max(Date_Calendar)),-12))) <=$(=Addmonths(today(today(Date_Calendar),),-12))"}
, Type_Facture = {'FACTURES'}, Site_Facture = {'XXXX'}, Code_Client-={'CLIENTSX'}>}Montant_HT_Ligne_Facture)

I used your first expression and in order to calculate the last year, I just removed 12 months by using AddMonths(..., -12).

I hope it helps!

Best regards

Hugo Romeira

[brunobertels · Accepted solution]

hi 

may be this 

this second expression return a timestamp for you use dayend () : 

dayend(today(Date_Calendar),-365))

so same day last year should be 

[vinieme12]

CurrentYear YTD

Sum({<Year=, Quarter=, Month=, Week=,

Date_Facture=,

Date_Facture={">=$(=Date(YearStart(Max(Date_Calendar)),'YYYY-MM-DD')

<=$(=Date(Max(Date_Calendar),'YYYY-MM-DD')) "}

, Type_Facture = {'FACTURES'}, Site_Facture = {'XXXX'}, Code_Client-={'CLIENTSX'}>}Montant_HT_Ligne_Facture)

Previous Year YTD

Sum({<Year=, Quarter=, Month=, Week=,

Date_Facture=,

Date_Facture={">=$(=Date(YearStart(Max(Date_Calendar),-1),'YYYY-MM-DD')

<=$(=Date(addyears(Max(Date_Calendar),-1),'YYYY-MM-DD')) "}

, Type_Facture = {'FACTURES'}, Site_Facture = {'XXXX'}, Code_Client-={'CLIENTSX'}>}Montant_HT_Ligne_Facture)

Replace YYYY-MM-DD in the above expressions to match the date format of the field