Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!
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.
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
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 !!
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.
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
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!!
I will try Daniel !
I am not a great programer but I will try an let you know !
Thanks again for all your help!
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)
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.
Thank you very much ! That was the only thing I need !!!
Thansk thanks a lot!!