Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
How you are identfying new clients???
When never exist before the selected year / month
Have a look in my example
Thk yu for your help
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
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
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
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
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
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
Nice 😉