AGGR COUNT MONTHS WHERE A PRODUCT WERE BUY FOR A CLIENT

[#1]

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 !!

[danieloberbilli]

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

[#3]

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 !!

[jagan · Accepted solution]

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.

[danieloberbilli]

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

[#6]

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!!

[#7]

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

Thanks again for all your help!

[danieloberbilli]

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)

[jagan]

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.

[#10]

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

Thansk thanks a lot!!