Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I wish to have a text box expression which counts a field (enq_Id) based on a varying week class.(either Current Week or Week-1)
This is currently working where it count all Enquiries recieved in the current week
='Enquiries:'&num(COUNT(if(Week_Class=('Current Week'),Enq_Id)))
but I also wish to display the previous weeks result and the increase or decrease between current week and week -1.
I have tried various variations on the following
='Enquiries:'&num(COUNT(if(Week_Class=('Current Week'),Enq_Id))) & if (num(sum(COUNT(if(Week_Class=('Current Week',Enq_Id) -COUNT(if(Week_Class=('Week-1',Enq_Id,' enq INCREASE ',' enq DECREASE ')&'on prior week'
I cannot get the increase/decrease calculation to work no matter how much I adjust the syntax.
Any ideas?
Hi,
i rewrote your expression.
Please, try this:
='Enquiries:'&num(COUNT(if(Week_Class=('Current Week'),Enq_Id))) & chr(10) &
if ( COUNT(if(Week_Class=('Current Week'),Enq_Id)) -COUNT(if(Week_Class=('Week-1'),Enq_Id)) >0,' Enq increased ',
if ( COUNT(if(Week_Class=('Current Week'),Enq_Id)) -COUNT(if(Week_Class=('Week-1'),Enq_Id)) <0,' Enq decreased ', ' Enq is the same ' ))
&'compared to the previous week'
Tell me if it works for you.
Rgds,
Erich
Hi,
i rewrote your expression.
Please, try this:
='Enquiries:'&num(COUNT(if(Week_Class=('Current Week'),Enq_Id))) & chr(10) &
if ( COUNT(if(Week_Class=('Current Week'),Enq_Id)) -COUNT(if(Week_Class=('Week-1'),Enq_Id)) >0,' Enq increased ',
if ( COUNT(if(Week_Class=('Current Week'),Enq_Id)) -COUNT(if(Week_Class=('Week-1'),Enq_Id)) <0,' Enq decreased ', ' Enq is the same ' ))
&'compared to the previous week'
Tell me if it works for you.
Rgds,
Erich
Thanks for the reply Erich, it does work.
Much appreciated.
Matt