Greater Than Expression Issue

[#1]

Hi All,

I am trying to find the total number of transactions over the course of a promotion lifetime. Currently I have the below:

'BW - ' & Count({<[Location]={'LocationA'}, [Date]={">=$([Promotion Start Date])<=$([Promotion End Date])"}>} DISTINCT [Sales invoice])

In tables, promotion dates are linked to promotion names. These in turn are linked to promotion products.

i.e.

PROMOTIONS

Promotion	Promotion Start Date	Promotion End Date
PROMOTION-1	01/01/2016	31/01/2016
PROMOTION-2	01/02/2016	31/03/2016

PROMOTION PRODUCTS

Promotion	Product Code
PROMOTION-1	PRODUCT-A
PROMOTION-1	PRODUCT-B
PROMOTION-2	PRODUCT-A
PROMOTION-2	PRODUCT-C

The hope is, when I filter to, say PROMOTION-1 via filter pane, the value (KPI) will update to show the total count of [Sales invoice] over that promotion period (01/01/2016 - 31/01/2016). However, it is pulling the total number of transactions in the period ONLY where a promotion product is included.

Where I would hope to see 2000 transactions, I am instead seeing 150 etc.

Any assistance would be much appreciated.

Regards,

Michael

[swuehl · Accepted solution]

Try clearing your selection in Promotion in the set expression (which will filter the product codes and thus the invoices, either by clearing only the field or using set identifier 1 (which will ignore all other selections as well. Not that the dollar sign expansion in the search expression is still considering your selection (though I think it should be written differently):

Count({<[Location]={'LocationA'}, [Date]={">=$(=Min([Promotion Start Date]))<=$(=Max([Promotion End Date]))"}, Promotion= >} DISTINCT [Sales invoice])

or

Count({1<[Location]={'LocationA'}, [Date]={">=$(=Min([Promotion Start Date]))<=$(=Max([Promotion End Date]))"}>} DISTINCT [Sales invoice])

[#3]

I used the first option and it seems to have worked. Just need to do some verification on the numbers but it looks promising.

Thank you very much.