Hi, I have a problem that I can see lots of people have had previously. I have tried unsuccessfully to try and get a suitable solution.
I’m trying to compare 'Distribution' (calculated field ‘in Stock / Number of records’) of the last two weeks vs previous two weeks and then the % difference.
Distribution is when the test is carried out is in stock 100% distribution or tested and out of stock 0% at the SKU level. This is when an 'SM' calls to a location, they record whether the item is in stock or not. (In stock = 1 in stock, 0=out of stock).
Previously we would tag/ hardcode the 'last two weeks calls' in the data source and then use an ’if statement’ to ascertain what records were applicable and that was working fine.
if [Last Two Weeks]="Last Two Weeks" THEN ([In Stock]) end)
SUM(if [Last Two Weeks]="Last Two Weeks" THEN ([number of records])
However, I am attempting to use a relative or anchor date now and this is where the problem arises.
I can get it to give me a correct answer for last two week and previous using a formula with no aggregation and then average but then I can’t subtract two averages to give me the difference. this obviously is not ideal
Distribution no Agg => [In Stock]/[Number of Records]
[Actual Call Date] <= [Anchor Date]
AND (DATEDIFF('day',[Actual Call Date],[Anchor Date])<= 14)
AND (DATEDIFF('day',[Actual Call Date],[Anchor Date])> 0)
) THEN [Distribution no Agg]
To further complicate things further I have a 'change view' parameter so I’m not sure whether the LOD route is suitable. Can somebody help me understand the level of details in this scenario? for some reason, I think its something to do with callid but I am obviously not understanding the scenario correctly. I I have included a workbook any assistance would be very appreciative.
I understand that there is an aggregation somewhere that I can't see maybe its the way the data is laid out as each call as a unique id where they may or may not test an individual SKU but that's a guess. I would really appreciat asoe help with this please as i have been going round in circles for the last couple of days,
Message was edited by: Rob Freyne
DummyWorkbook.twbx 41.8 MB