Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Invoice ID Count

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

6 Replies
jwjackso
Specialist III
Specialist III

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
Contributor II
Contributor II

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
Not applicable
Author

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
Specialist III
Specialist III

Do you have a sample data set?

Anonymous
Not applicable
Author

No sorry Im working on server,

Thank you .

dionverbeke
Luminary Alumni
Luminary Alumni

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...