Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a scenario which is probably easy, but I can't seem to get to work.
I have the following tables with the fields I care about here:
Meeting, with Meeting info including Meeting date.
MtgID
MtgDate
90DaysAfter (date prepopulated during load)
MeetingCustomer with Customers who have attended that meeting.
CustID
MtgID
CustomerOrders with Orders those Customers have purchased and the date of purchase.
CustID
OrderID
OrderHeader table with Orrder Date
OrderID
OrderDate
OrderDetail with the actual SKUs the Customers purchased.
OrderID
OrderLineID
SKUNumber
Quantity
SalePrice
I need to report on the meetings, which customers attended them, and what SKUs they purchased 90 days before and after to show if attending the meeting encouraged them to buy how much and which particular products.
I have tried a chart with MtgID, CustID, OrderID, SKUNumber as dimensions and then the expression for the 90 day after column as:
if(num(OrderDate) <= num([90DaysAfter]) and num(OrderDate) >= num(MtgDate), sum(Quantity*SalePrice))
When I tested with just sum(Qty*Price) I get exactly what I want. When I added in the date range part of the calculation which it needs to pull from the Meeting table, I get nothing. Even if I take out the actual range calculation and just say
if(num(OrderDate) <= num(Today()) , sum(Quantity*SalePrice)) then I get nothing. But if I just say
sum(Quantity*SalePrice), it's fine. It seems like asking it to reference a date up in the Meeting table is the problem.
Am I setting it up right?
if(num(OrderDate) <= num([90DaysAfter]) and num(OrderDate) >= num(MtgDate), sum(Quantity*SalePrice))
Try below code
SUM({<OrderDate = {"<=$(=90DaysAfter) >=$(=MtgDate)"} >}Quantity* SalePrice)
This gave me the values and it correctly gives me the totals but now all orders are showing up within the 90DaysAfter column even though the order date is from a year prior. So this column is populated for every single order. Please help - this is defintely the right track it's just not quite right.