topic Re: Count New Shops For Products In This Year in QlikView

Count New Shops For Products In This Year

adamgaox — Mon, 04 May 2015 16:10:58 GMT

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.

Re: Count New Shops For Products In This Year

Gysbert_Wassenaar — Mon, 04 May 2015 16:22:10 GMT

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)

Re: Count New Shops For Products In This Year

adamgaox — Mon, 04 May 2015 16:25:43 GMT

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?

Re: Count New Shops For Products In This Year

buzzy996 — Mon, 04 May 2015 16:37:55 GMT

try this,

Re: Count New Shops For Products In This Year

buzzy996 — Mon, 04 May 2015 16:40:46 GMT

small correction in expression

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

Re: Count New Shops For Products In This Year

Gysbert_Wassenaar — Mon, 04 May 2015 16:51:29 GMT

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.

Re: Count New Shops For Products In This Year

ramoncova06 — Mon, 04 May 2015 17:33:30 GMT

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

Re: Count New Shops For Products In This Year

antoniotiman — Mon, 04 May 2015 18:21:35 GMT

Hi Adam,

see attachment.

Regards,

Antonio

Re: Count New Shops For Products In This Year

adamgaox — Mon, 04 May 2015 23:20:04 GMT

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

Re: Count New Shops For Products In This Year

adamgaox — Mon, 04 May 2015 23:21:27 GMT

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

Re: Count New Shops For Products In This Year

adamgaox — Mon, 04 May 2015 23:33:30 GMT

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.

Re: Count New Shops For Products In This Year

adamgaox — Mon, 04 May 2015 23:35:46 GMT

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