how to setup chart

Fri, 06 Dec 2013 06:55:49 GMT

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?

Re: how to setup chart

MK_QSL — Fri, 06 Dec 2013 09:42:54 GMT

if(num(OrderDate) <= num([90DaysAfter]) and num(OrderDate) >= num(MtgDate), sum(Quantity*SalePrice))

Try below code

SUM({<OrderDate = {"<=$(=90DaysAfter) >=$(=MtgDate)"} >}Quantity* SalePrice)

Re: how to setup chart

Fri, 06 Dec 2013 18:24:53 GMT

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.

topic Re: how to setup chart in QlikView

how to setup chart

Re: how to setup chart

Re: how to setup chart