topic Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT in QlikView

AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

Wed, 08 Apr 2015 01:19:23 GMT

Hi,

I would like to know if there is posible to With this table

Sells:
MonthYear,
Client,
Product

to create an expression that COUNT de quantity of clients that buy a product in the diferents 12 months of the year.
The table only have the dimenson of the product.

Then, I will have to do the same but for those clients that only buy the produc in 11 months of the year.. and one expresion for the 10 months and one expression for the 9 etc...

At least the user will be able to select the 12 months stanting in the monthYear he prefer.

Please, help me, I do not know how to do this !!

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

danieloberbilli — Wed, 08 Apr 2015 02:11:51 GMT

I think you can do this with aggr() dynamically, but an easier approach would be to calculate the number of month first in the script and then simply use set analysis:

Please find qvw example attached:

Script:

Data_Inline_sum:

Left Join

LOAD

count(MonthYear) as MonthYear_count,

Client,

Product

Resident Data_Inline Group By Client, Product;

In the Chart object:

Dimension: Product

Expression 1: count({<MonthYear_count={12}>} DISTINCT Client)

Expression 2: count({<MonthYear_count={11}>} DISTINCT Client)

and so on

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

Wed, 08 Apr 2015 02:41:59 GMT

Hi Daniel !
Thank you very much for your help !!

I am not sure if I can use your answear, but let me tell you it is a great idea anyway.

The problem is that I do not have all my fiels in the same table, I am using a Link Table and they are in differents tables.

The other point is that I am using YearnMonths fields so the user uses a SLIDER control to select the 12 months. That's why they can chose for example 201306 to 201405. In this case the expresion for month 1 refers to June of 2013 and the expression for month 2 refers to July 2013. But I will depend of the user selection.

Please, tell me if I there is anyway to solve this. I am out of time!

Thansk you very much for your concern !!

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

jagan — Wed, 08 Apr 2015 02:46:14 GMT

Hi,

You can try like this

Dimension : Product

For 12 Months:

Sum(Aggr(If(Count(DISTINCT MonthYear) = 12, 1, 0), Client)

For 11 Months:

Sum(Aggr(If(Count(DISTINCT MonthYear) = 11, 1, 0), Client)

For 10 Months:

Sum(Aggr(If(Count(DISTINCT MonthYear) = 10, 1, 0), Client)

Hope this helps you.

Regards,

Jagan.

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

danieloberbilli — Wed, 08 Apr 2015 02:50:36 GMT

I think the fact that your fields are in different tables (and using a link table) could it even be more difficult in an aggr() function as you might get unexpected/wrong results. I would generally recommend to join all necessary fields in one seperate table if you would like to go with an aggr() approach.

Just as a note: maybe you can also think about using variables for the slider instead of the field - you could use the set analysis filter from my example the same way.

Kind Regards

Daniel

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

Wed, 08 Apr 2015 03:20:33 GMT

Hi Jagan!
Thanks a lot for your help.

I seems to be working, but only when you select un procuct in particular. If you do not select anyone the values change. I do not know how to fix it.. but you are very close !

Thansk again!!

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

Wed, 08 Apr 2015 03:21:45 GMT

I will try Daniel !
I am not a great programer but I will try an let you know !

Thanks again for all your help!

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

danieloberbilli — Wed, 08 Apr 2015 03:27:02 GMT

maybe if you add ,Product right after Client as last element of the aggr()

Sum(Aggr(If(Count(DISTINCT MonthYear) = 12, 1, 0), Client, Product)

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

jagan — Wed, 08 Apr 2015 03:49:48 GMT

Hi,

Try adding Product as another parameter to Aggr()

For 12 Months:

Sum(Aggr(If(Count(DISTINCT MonthYear) = 12, 1, 0), Client, Product)

Regards,

Jagan.

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

Wed, 08 Apr 2015 04:33:35 GMT

Thank you very much ! That was the only thing I need !!!

Thansk thanks a lot!!

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

danieloberbilli — Wed, 08 Apr 2015 04:37:12 GMT

Thanks Rodrigo - but maybe you should rather mark Jagan's last post as correct as I was just adding something to his idea.

Thanks anyway

Kind Regards

Daniel

Re: AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

Wed, 08 Apr 2015 12:02:27 GMT

You are right Daniel !

Thanks for been so correct person, it is nice to work with this kind of people !

Thanks a lot again !