Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have the following pivot table in which I'm counting the number of customers who got invoices transactions:
count(distinct {<TRANS_TYPE={'invoice'}>} CUSTOMER_KEY)
what I want now is to add another column which counts the lost customers per month; a lost customer is the one who hasn't got any invoice transaction in the period 12 months prior to each month in the pivot table
so let's take the first business unit (scrambled here) now next to Jan 2016 I want to get the count of customers who haven't got any invoice transaction in the period which is between 1 Feb 2015 and 31 Dec 2015
next to Feb 2016 I want to get the count of customers who haven't got any invoice transaction in the period which is between 1 Mar 2015 and 31 jan 2016 and so on
my problem is in how to get the variable date range per row which depends on the month displayed
so in peudo code the lost customers are the ones excluded in the range of dates which depend on the displayed month year
please advise
if you have numeric YEAR and MONTH Fields, then your comparison periods will be as below
>=Addmonths(MakeDate(Year,Month,1),-11) < (MakeDate(Year,Month,1))
another way would be to add a flag for each customer for each month and then refer the flag in set analysis; should be easier
you mean something like this?
COUNT
(DISTINCT
{
<
CUSTOMER_KEY=E(
{
<
TRANS_DATE={'>=$(=NUM(DAYSTART(ADDMONTHS(MAKEDATE(YEAR,MONTH_NUM,1),-11)))) <$(=NUM(DAYSTART(MAKEDATE(YEAR,MONTH,1))))'}
>
}CUSTOMER_KEY
)
>
}
CUSTOMER_KEY)
yes
may i share with you a sample document?
I got a customer whose name is 3 Chefs
this customer has 11/9/2015 as max date on which he go invoiced
However when I create a chart with Business_Unit_Name, and Month_Year as dimensions and use the above expression it is not being counted
Please advise
yes, please share a sample app
I uploaded a sample qvw file
please advise
the above expression that I used has no effect
it is getting the number of customers that are invoiced in the selection of dates
I selected 2016 and the condition which is
CUSTOMER_KEY=E(
{
<
TRANS_DATE={'>=$(=NUM(DAYSTART(ADDMONTHS(MAKEDATE(YEAR,MONTH_NUM,1),-11)))) <$(=NUM(DAYSTART(MAKEDATE(YEAR,MONTH,1))))'}
>
}CUSTOMER_KEY
)
has no effect ; the result is the same if I omit this
Sorry, I have been busy. I will definitely look at this and come back to you