Count New Shops For Products In This Year

[adamgaox]

Hi All,

I've got a requirements from my customer,the requirements like the follow:

SHOP_SALES:
LOAD * INLINE [
     PRODUCT, SHOP, YEAR
     A, H1, 2014
     A, H2, 2014
     A, H3, 2014
     A, H4, 2014
     B, H1, 2014
     B, H2, 2014
     B, H3, 2014
     C, H3, 2014
     C, H4, 2014
     A, H5, 2015
     A, H4, 2015
     A, H3, 2015
     B, H5, 2015
     B, H4, 2015
     B, H3, 2015
     C, H1, 2015
     C, H2, 2015
     C, H3, 2015
];

PRODUCT is the dimension

to count the distinct new shop of the product in this year

AS in 2014,A is sold to H1-H4

AS in 2015,A is sold to H3-H5

so the H5 is the new shop

Result Should Be:

PRODUCT     NEW SHOPS

A                    1

B                    2

C                    2

but I use the PRODUCT AS DIM

Count({<YEAR={'2015'},HOSPITAL=p({<YEAR={'2014'}>}HOSPITAL)>}DISTINCT HOSPITAL)

but it seems that the dimension outside is not available for the p() function.

Is there any good way to cover this requirements,thanks in advance.sorry for my poor English.

[adamgaox]

Thanks,Gysbert.

I think it's a good method to add field in the structure,But I prefer the expression first.

Thanks for your exists() function,I learned a new use of that.

[adamgaox]

Thanks Antony,This is what I want.Aggr() is really powerful.