Qlik Community

QlikView Expressor

Discussion Board for collaboration related to QlikView Expressor.

el_lloydie
New Contributor III

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

1 Solution

Accepted Solutions

Re: Simple expression help

can you try this

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

14 Replies
effinty2112
Honored Contributor

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

arvind_patil
Valued Contributor II

Re: Simple expression help

HI Gareth,

Please try below code It May Help You:

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



Thanks

Arvind Patil

el_lloydie
New Contributor III

Re: Simple expression help

Thanks guys, unfortunately neither are working.

Expression currently sits as

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

arvind_patil
Valued Contributor II

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

effinty2112
Honored Contributor

Re: Simple expression help

Hi Gareth,

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

Andrew

el_lloydie
New Contributor III

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

el_lloydie
New Contributor III

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

effinty2112
Honored Contributor

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)

passionate
Valued Contributor

Re: Simple expression help

Hi Gareth,

Please provide some sample Data.

Regards,

Pankaj

Community Browser