14 Replies Latest reply: Jul 18, 2017 7:19 AM by Pankaj Thakur

# Simple expression help

Morning guys

Quelle surprise, I'm after a little help please.

I’m trying to find the number of PO’s raised retrospectively. Logically, the way to work this out would be to look where the order date is after the invoice date.

My expression is as follows but doesn’t work. I only want it to count the positive values.

count({<[Invoice Date]-[Order Date]={'>0'})

Thanks in advance for any help offered.

Gareth

• ###### Re: Simple expression help

Hi Gareth,

Maybe:

Count(if([Invoice Date]>[Order Date],POid)

Where POid is a unique identifier for your purchase orders.

Cheers

Andrew

• ###### Re: Simple expression help

HI Gareth,

if([Invoice Date]>[Order Date],Count(Distinct POid)

Thanks

Arvind Patil

• ###### Re: Simple expression help

Thanks guys, unfortunately neither are working.

Expression currently sits as

if([Invoice Date]>[Order Date],Count(Distinct [PO Number]))

• ###### Re: Simple expression help

Hi Gareth,

May be there is an issue in date format. So cloud you provide sample data or else please check date values Please convert into numbers and check:

if(Num(Date([Invoice Date]))>num(Date([Order Date])),Count(Distinct [PO Number]))

Thanks,

Arvind Patil

• ###### Re: Simple expression help

Hi Arvind,

The date formats are the same, but in any event I used your updated expression and it still returned NULL.

Thanks,

Gareth

• ###### Re: Simple expression help

Hi Gareth,

Does every PO have an Invoice Date and an Order Date?

Andrew

• ###### Re: Simple expression help

Hi Andrew,

Not every PO will have an invoice date because not every PO will have been raised retrospectively. It's the number of retrospective PO's I'm trying to ascertain.

Thanks,

Gareth

• ###### Re: Simple expression help

Try this if the Invoice Date is null if an invoice has not been raised:

Count(if([Invoice Date]>[Order Date],POid) + Count({\$-<[Invoice Date] = {"*"}>}POid)

• ###### Re: Simple expression help

Hi Gareth,

Regards,

Pankaj

• ###### Re: Simple expression help

Hi all

Sample data attached.

Thanks

Gareth

• ###### Re: Simple expression help

can you try this

count({<[PO Number] ={"=[Invoice Date]-[Order Date]>0"}>} distinct [PO Number] )

• ###### Re: Simple expression help

Bingo, you've got it nailed Kushal.

Many thanks all for your help with this! It is much appreciated.

Gareth

• ###### Re: Simple expression help

Hi Gareth,