List of Customers in a variable

[alec1982]

hi,

trying to store the list of customers that we have done business with between two dates into a a variable but no luck..

anyone has a good logic?

Thanks,

Alec

[sunny_talwar]

I think you will need to provide more information before we can offer any help

[alec1982]

hi Sunny,

Table:

Sales Rep, Customer, Sales Date, Revenue

ABC,     XYZ,     10,1,2014    , 1000

qas,     qas,     11,1,2014, 0

qwe,     wed, 12,1,2015, 200

need to store the list of customers with revenue greater than 0 and the sales occurred between 10,1,2014 to 11,15,2014.

the variable should return ABC.

Note: the dates will be selected by the user on the UI

I have tried the following so far:

if(Sum({$<DateofReport ={ ">=$(=$(vStartDate))<=$(=only(OBSDate))"} >}REV) <>0,

chr(39) &Concat(Distinct (CUST),chr(39)& ',' & chr(39)) &chr(39))

[sunny_talwar · Accepted solution]

May be like this:

=Concat(DISTINCT {<[Sales Date] = {"$(='>=' & Date(MakeDate(2014, 10, 1), 'DateFieldFormatHere') & '<=' & Date(MakeDate(2014, 11, 15), 'DateFieldFormatHere'))"}, Customer = {"=Sum(Revenue) > 0"}>} Customer)

UPDATE: forgot to add the second contion.

[Anonymous]

hi

try this

Only({$<SalesDate={'>=$(=Date(vFromDate, 'YYYY-MM-DD'))<=$(=Date(vToDate, 'YYYY-MM- DD'))'}>} customers)

if (aggr(sum(1), Salesdate)>0 , $(=concat(distinct customers, ' , ')))

[alec1982]

that helped.. thank you