I’m trying to look at a report to show a decline in product sales by item code, but taking into account customers, not just global declines.
For example, ten customers may buy a product, 9 are increasing sales but the 10th is declining.
If the actual total sales are increasing this wouldn’t show in the report as it’s not declining, but i want to see the one customer that isn’t.
At the point I run the report, I won’t know which customer or product I want to look at, that’s the point of the report. It should basically give me a list of all declining lines by customer.
Any ideas?
What constitutes a decline? Is the from one month to the next, or does it have to decline for several months?
Say three months, consecutively.
I’ve got it for customer and product separately, but what I really want is both combined so the products that decline for one customer might not be the same that decline for another.
Equally, a product that is growing globally but declining for a specific customer would not show in the normal way of doing these.
OK. I don’t have a solution right off… I just wanted to play around a bit to see if I could come up with any ideas.
@StuartH Let me know if this isn’t what you’re looking for, but I quickly ran sales volume on the Product dimension in Variance Mode. Then sorted low to high by the Variance column. Put it into a Nested Table, then dragged and dropped my BT Customer dimension as the higher level. That way, I see my customers, and can expand into their product lists. It is at that point showing me the top DECLINING products for the customer and period selected. This won’t necessarily show you products declining three months in a row, but it’s a start.
Hello! You can do this with right click filtering. Select the period you want to start with, right click and then say “month” LESS THAN “a previous month” - see the screenshot. Remember, if you want to make this a favorite, to edit the Advanced Search it creates, to use Offset Dates, so the Favorite will stay current as a month ends and a new one begins.
Thanks Tim, I see what you’re doing there and as you say it’s not going to show declining on both dimensions but I’ll have a look - maybe using this logic I could get declining & under budget or something.
Thanks John, I’m already aware of how to do declining, my issue is that I want declining products for a declining customer - from what I can see it is not applying both filters at the correct levels.
Currently I’ve built a query for declining 3 month trend on Product Code.
I’ve then switched into Nested mode and added Customer.
Some of the numbers flip into positive meaning the filter is not being applied correctly - can’t quite figure out what Phocas is doing.
Stuart, we also accomplish this on a couple of our dashboards. One widget dedicated to declining customers in a specified time period. Then the next widget over is price line declining, then the next widget over is declining products. That way, as you focus in, the filter is layered and applies across the board.
Thanks Tim, I’ll try and demonstrate my idea a bit more tomorrow but essentially if you picture a set of products sold to a single customer.
The customers total sales are increasing, so the customer wouldn’t show on a declining sales report.
A particular item is also increasing so wouldn’t show on the report for declining items either.
However that item to that specific customer is declining.
They’re the lines I’m trying to identify.
I see, Stuart. I suppose you could find that information with either the nested table or the dashboard widgets I mentioned, but it may not be quite as straightforward as it sounds like you are hoping to make it. If you find a better way, I’d love to hear it!
I don’t know if you have access to the database designer or not. If you do, you could create a dimension that is a combination of Customer and Product. That would make it quite easy to achieve what you are trying to do.
1 Like
I like this - it certainly could work as i’m trying to force focus to consider both Properties at the same time (in parallel) not one after the other (in series). Annoyingly this is how I would expect Nesting to handle it but I guess without the link field it can’t.
I don’t have access to DD but we’re doing some development so can link it with that.
Could you expand on this a bit? I’m trying to picture it in my head, but struggling a bit. I’d be interested to try this in my DB designer.
My understanding is that it’s concatenated field:
Customer - A, B, C etc
Product - 1, 2, 3 etc
Results would show as:
Interesting. Do you think that would make this work easier as a dimension then? Instead of identifying which customer you want to look at, focusing in, then looking at their product list? Or that column would essentially be a helper column for functionality within the nested table?
If you use that approach I would like to see how that turns out. Please post a couple of snapshots if you can.
I think it would have to be a top level dimension in order for the date filter to work.
