Additional question to topic "Customers who buys more than 1 (selected) products"

[Teppo_Ojala]

Hi again

I still have one challenge in this case. I managed to show the kpi in text box, but in chart this is still not working.

My case is about magazine subscriptions. And in the app I need to show this KPI trend by months. I added a new field to this app, which is yearmonth. I would like to have an expression that calculates the customers per months who orders 2 or more magazines during that month.

In the sample data I added a new column "Date" which includes two values (2013-01, 2013-02). In 2013-01 I have 3 customers who orders 2 magazines, In 2013-02 I have only 2. Kim orders only one magazine there.

In the app I have 2 text boxes that shows correct values. In the bar chart you can notice that 2013-02 is wrong.

Could you help me? I have tried quite many formulas already.. but still not working.

THANKS

[Nicole-Smith · Accepted solution]

See the attached.

[Teppo_Ojala]

Thanks,

It seems that aggr is the only way to handle this. WIth the amount of the data this may cause some performance issues - but I have to try this one. If somebody has new ideas, please write to me..

Thanks a lot!

[Nicole-Smith]

Another way you could do it is to do the calculation in the load script and just flag Customers with a 1 who have bought more than one product in a month.  Then you could just sum that flag.  I can work on pulling a sample app together.

[Nicole-Smith]

See the load script and new chart expression in the attached.

[Teppo_Ojala]

Hi,

I know the flag logic and I use it in this project also. But in this case it is not usefule, because end user can choose x products (=magazines in this case) and we have to check in trend graph which customers orders 2 or more of the chosen products. So, flagging in the data model is not a solution in this case.

Thanks any way!

[Nicole-Smith]

You can still do it with the flagging (see attached).  You can have them select whatever number they'd like in an input box, and use that number in the expression calculation.

[Teppo_Ojala]

But is this possible:

Let's think they have 50 different products. End user randomly selects 4 different products (lets say product 10, product 20, product 30 and product 40). In the graph we have to show how many percentages of customers who have bought atleast one of these (count distinct customer) has bought atleast 2 of these chosen products. I don't need an input box for changing a value 2. It is also 2. With dynamic selection I mean the set of chosen products.

Teppo

[Nicole-Smith]

Can you post a sample .qvw with how data would look in such an app?  And what you would expect to see?

[Teppo_Ojala]

Unfortunately I can't send the app with customer's data. But the idea is very similar to this app in the attachment.

The aggr-function returns exactly correct value, but my only concern is the performance. In my app I have about 60 products, but over 1 million customers - that's why aggr over customer can be too heavy.

I am going to customer tomorrow and I try this aggr-function over there with the big amount of data.

I will also try to build the trend chart with 12 different expressions (END_OF_EACH_MONTH changes) using conditional show per expression (YTD mode, so I will only show the months from ongoing year)

count({<customerid = {"=Count(Distinct if(Date=END_OF_EACH_MONTH,Product))>1"}>} distinct customerid)

Teppo