Dashboard: The new clients customers of the month

[zarmoton]

Hello

My problem seems not so complex but i haven't find the good solution !!!

Help please.

I need to report in my application the new client of the month (or year)

After selecting the year, the month I would like to show all new clients (where never exist before the selected month/year)

Have a look in the attach file

Warm thank for your help.

Data is this one:

Year,             Month,   Client Name

2011	12	A
2011	12	AA
2012	1	A
2012	1	B
2012	1	C
2012	2	B
2012	2	C
2012	3	B
2012	3	C
2012	3	D
2012	3	E

if I select 2012, and month = 1, then i need to show in the list box "New clients" with B, C
if I select 2012, and month = 2, then i need to show in the list box "New clients" with Nothing (empty list), or "No new client this month"
if I select 2012, and month = 3, then i need to show in the list box "New clients" with D, E

and if possible (nice to have)
if I select 2012 only with no selection on the month, then i need to show in the list box "New clients" with B, C, D, E

Hope i'm clear enough.

[sivarajs]

How you are identfying  new clients???

[zarmoton]

When never exist before the selected year / month

Have a look in my example

Thk yu for your help

[Jason_Michaelides]

Here you go - see attached.  I have used the expression:

=Concat({<[Client Name]=E({1<Period={"<$(vPeriod)"}>}) >}  DISTINCT [Client Name],', ')  where vPeriod is the minimum selected period. (Period is a new field defined in the load script joining Year and Month to make things easier.

Hope this helps,

Jason

[zarmoton]

Thank you Jason for your help

How to have the "New to selected period" not in a text box, but in a list/table box (one client per row) ?

And because my database is quite huge, have you an idea to do it, but without to add another field (period) ?

Thank again

[zarmoton]

Thank you Jason for your help

How to have the "New to selected period" not in a text box, but in a list/table box (one client per row) ?

And because my database is quite huge, have you an idea to do it, but without to add another field (period) ?

Thank again

[zarmoton]

Thank you Jason for your help

How to have the "New to selected period" not in a text box, but in a list/table box (one client per row) ?

And because my database is quite huge, have you an idea to do it, but without to add another field (period) ?

Thank again

[Jason_Michaelides · Accepted solution]

The larger your dataset, the more important it is to push as much as possible into the script.  It may take a little longer to load (in this case it will be negligible) but you'll save in server processing at runtime so your users will have a better experience.  I'd reccomend you create this field in the load script otherwise instead of one field in the set analysis you will have to use 2 (Year and Month).  Over large datasets this may make a difference.

I'm not sure how you'd get it in a listbox, but I've done it in a chart for you.  See attached,

Hope this helps,

Jason

[#9]

Hi:

Write the following expression (suggested by Jason) in the List box under General-->Field-->Expression

=Aggr(Concat({<[Client Name]=E({1<Period={"<$(vPeriod)"}>}) >}  DISTINCT [Client Name],', '), [Client Name])

Thanks,

Narasimha K

[Jason_Michaelides]

Nice 😉