Invoice ID Count

[Anonymous]

Hi All,

I was trying to calculate number of invoices in last 12 months, I used the following expression but Im getting an error 

if(DATE(date#(paid_date_trend, 'YYYYMMDD') ,'DD/MM/YYYY')>=addmonths(today(),-12) , Count(distinct if(vSpendType = 'Sourceable', (if(is_sourceable = 'Y', [Invoice ID]))

               , if(vSpendType = 'SC Manageable', (if(is_sc_manageable = 'Y', [Invoice ID]))

               , [Invoice ID]))))

Variables I used :

vSpendType = 'Sourceable'

TIA

[jwjackso]

You probably want to use set analysis for the first part,

If('$(vSpendType)' = 'Sourceable',Count({$<is_sourceable={'Y'},paid_date_trend={">=$(Date(AddMonths(Today(),-12),'YYYYMMDD'))"}>}[Invoice ID]),

   If('$(vSpendType)' = 'SC Manageable',Count({$<is_sc_manageable={'Y'},paid_date_trend={">=$(Date(AddMonths(Today(),-12),'YYYYMMDD'))"}>}[Invoice ID]),

      Count({$<paid_date_trend={">=$(Date(AddMonths(Today(),-12),'YYYYMMDD'))"}>}[Invoice ID])))

[sjaiswal13]

addmonths(today(),-12) returns 3/12/2017, is it matching with the value returned by DATE(date#(paid_date_trend, 'YYYYMMDD') ,'DD/MM/YYYY')?, i think there is mismatch between the date format.

[Anonymous]

Hi Jerry,

Something wrong with your expression as the data isn't getting validated with the database and moreover if I change 12 to 24 to get las 24 months invoice count the values im getting are same as last 12 months count.,

Thank you .

[jwjackso]

Do you have a sample data set?

[Anonymous]

No sorry Im working on server,

Thank you .

[dionverbeke]

You should try to keep these expressions in the backend.

You should also try to combine multiple if's in one statement in the backend.

Your server cpu and ram usage will like you for that...