Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
nawazuddin234
Contributor III
Contributor III

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

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.

nawazuddin234
Contributor III
Contributor III
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 II
Specialist II

Do you have a sample data set?

nawazuddin234
Contributor III
Contributor III
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...