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.

[Gysbert_Wassenaar]

If your data is already ordered like your example, you can do this:

SHOP_SALES:

LOAD *, if(not exists(PRODUCTSHOP),1,0) as IsNewShop ;
LOAD *, PRODUCT & '|' & SHOP as PRODUCTSHOP 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

];

Dimension: Product

Expression: count({<YEAR={2015},IsNewShop={1}>}PRODUCT)

[adamgaox]

Thanks,Gysbert,As the structure is not available to change,this is a small part of my data model.

Can it be achieved in expression?

[buzzy996]

try this,

[buzzy996]

small correction in expression

=Count({$<YEAR={'2015'},SHOP={'H3','H4','H5'}>} Distinct PRODUCT)

[Gysbert_Wassenaar]

Technically yes. Should you want this? I don't think so. Don't make a mess in the front end if you can fix it easily in the script.

[ramoncova06]

you want to exclude the results that were bought on 2014, so use "e" insted of "p"

count({<SHOP = e({<YEAR={'2014'} >}) >}SHOP)

I do agree that if you can change this in the backend it would be better, since you would get better performance

[antoniotiman · Accepted solution]

Hi Adam,

see attachment.

Regards,

Antonio

[adamgaox]

Thanks Ramon,But I've tried before,dimension doesn't work for e() too

[adamgaox]

This may not be what I want Shiva.Thanks for your sharing.