Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I got a pivot table by Month Year
the user selects Jan 2016
and I want to count the number of customers that had only invoice transactions in Feb 2015
is this possible?
Feb 2015 is calculated from Jan 2016 i.e. it is the date 11 months prior to Jan 2016
please advise
use set analysis
Count{<MonthYear={$(=addmonths(MonthYear,-11)}>}Value
it maybe that you Need to Format the dateformat using date(=addmonths(MonthYear,-11,'YYYYMM')
and is it possible to list them instead of counting them
I mean next to Jan 2016 display the names of the customers who got invoiced only in Feb 2015
Hi
Not tested ..
may be like this ?
Assuming your field is Customername
Only({$<MonthYear={$(=addmonths(MonthYear,-11)}>} Customername)
or
=Concat({$<MonthYear={$(=addmonths(MonthYear,-11)}>>} DISTINCT Customername, ', '))