Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
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.