Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Invoice ID Count

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

Re: Invoice ID Count

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
New Contributor III

Re: Invoice ID Count

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

Re: Invoice ID Count

Do you have a sample data set?

nawazuddin234
New Contributor III

Re: Invoice ID Count

No sorry Im working on server,

Thank you .

Luminary
Luminary

Re: Invoice ID Count

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

Community Browser