Subtotal with Set Analysis

[#1]

Hello,

I'm fighting with an expression for years now, at least it feels like that, and cant find any solution. I appreciate any hint you could provide. Unfortunatly I am not experienced with autogenerating demo data and not allowed to publish any live data from my datamodell so i hope i can explain the problem good enough.

Strongly simplified, I got a sales table, containing 2 key columns, SalesDate, Quantity and Value per sold product transaction and 2 dimension tables (there are several more, but those arent relevant for this calculation) - one containing advertisement campaign infos with the columns CampaignProductKey, CampaignName, DateStart, DateEnd

the other one is the product dimension table containing ProductKey, ProductName, ProductGroup and a few other columns.

I got a report that actually looks like

CampaignProductKey, ProductName,

     sum(IF(SalesDate >=DateStart and SalesDate <= DateEnd, Value)) as Value,

     sum(IF(SalesDate >=DateStart-365 and SalesDate <= DateEnd-365, Value)) as ValueLastYear

Now i got a request if i can add a total of the productgroup per CampaignProductKey within the same date interval for actual and last year. I tried like a million combinations of set analysis with and without total or if-expression but i cant get correct numbers.

What makes it even more complicated is, that there is a chance that no campaignproduct is sold within the last year date period.

I really appreciate any hints you can provide.

greetings,

Markus

[teempi]

Hi Markus,

Some example data and expected results might be helpful. It seems though that you could use IntervalMatch to link your campaigns ans sales data (assuming I understood your problem correctly). After that it should be pretty simple with set analysis.

-Teemu

[#3]

Hi Teemu,

thanks for your reply. I built a strongly simplified data model (with excel data ) - no shop or date dimensions, which shouldnt be relevant for this task.

The CampaignSales chart actually shows the sold quantity per product within the given date period. I use "if" for the expression because as far as i know, set analysis doesnt work with column values.

The next expression should show the sold quantity of the whole group of the product within the same interval.That should be 140 for each of the first 2 products in the chart and 58 for the third, if i didnt calculate wrong now, but as you see, the expression (and several others containing a total syntax or similar i tried)  dont work as planned.

Could you please provide some additional info for the given example ? I never used the IntervalMatch command in my scripts until now.

[teempi]

Hi,

Check the attachment for some really obscure script

Your problem was a lot harder to figure out than I thought. I think you have to build some kind of logic to link campaign info for products that don't actually have a campaign in order to calculate totals by product group. I've done it by modifying the campaign data (in script) to include all the product in the same product group as the original product. I also had to change the excel files to my own since I needed to be able to reload the data. It should be pretty easy to revert back to the originals.

I hope this gets you on the right track!

-Teemu

[#5]

Great idea, never thought of expanding the campaigns table. I rather tried to get it the other way and was trying to link the campaign table somehow to the product and/or calendar dimension without creating a circular reference but without success.

I have to admit i am a bit worried about the performance with the productive data, as the real product table has about 500.000 entries and the sales nearly 13 millions. I'll use just the productID key to link the table without a synthetic one which includes the sales date, because I also have to add an expression showing the past year's sold quantity of the same product/group.

Nevertheless I'll give it a try with the live data and let you know if it works as intended and with good performance

[RedSky001]

I also was playing around with this and found it very tricky!  In the end I found the easiest way to do this was just to create two fact tables, joined by Date and product group.  So Fact 1 has the grannular level of detail and fact 2 had the summary level of grouping.

Anyway check out the attachment.  I'm not 100% happy with having two quanity fields, but it works!

Mark

[teempi]

Yep performance was a concern when I came up with that solution and some optimizations are surely a good idea. I was hoping to just push you in the right direction

-Teemu

[#8]

Another good idea.

Although i usually try to avoid precalculated measures, i think performance-wise this would be the more favorable solution. I just have to make sure, that the link between campaign products and fact2 table is guaranteed, even if there are no sales on specific days - especially when theres still the shop-dimension to consider. I just have to generate fact1 entrys per product/campaign day/shop. Should be overall less data than the other way

I hope there will be a out of the box - way sometimes in qv for such tasks:)

But I thank you both very much for your approaches