Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I would like to create a chart which shows on a month by month basis which customers have not brought anything that month.
I am not sure what is the best way to go about this.
Thanks
Andy
Hi,
Can you post a sample file because we need clarify the ur requirements? if am guess it like this
=count({<month={"=Count(Customer)<0"}>}custcount)
Regards,
Create a Pivot Chart with below
Dimension 1 : =if(Aggr(SUM(OrderValue),[Customer Name],OrderMonth)<0,[Customer Name])
//This is a Calculated Dimension
Dimension 2 : OrderMonth
Expression SUM(OrderValue)
That didn't seem to work, it just showed a count of the orders each month.
I have attached a qvw file to post
Thanks
Andy
Dimension: OrderMonth
Exp: if((if(count([Customer Name])>0,0,1))=1,[Customer Name])
Hi,
PFA,
hope its help.
Regards,
You need to create a flag.
IF(Previous(CustomerId)=CustomerID, 0, 1) as NewCustomer
the table should be ordered by customerID and orderdate/purchase date ASC
The flag will help you in identifying new/ old customers
Hi ,
Here is a modification i have done to the code of your sample
Sales:
LOAD * INLINE [
Month, Customer, Value
Jan, C001, 10
Jan, C002, 20
Feb, C002, 30
Mar, C001, 40
];
Months:
LOAD * INLINE [
Month,
Jan,
Feb,
Mar,
Apr,
May
];
FactCalculation:
LOAD
Month, Customer, Value ,
if(Value=null() or Value=0,1,0) as NoCount
Resident Sales;
DROP Table Sales;
i have used a flag and named it NoCount.
Now you can easily create a graph with month as Dimension and Expression as sum(NoCount)
Here is the sample attached.
try in chart or pivot table
add dimension month
expression sum(if(Sales<0,Sales))
in presentation tab uncheck on show zero value
then you 'll see