Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I need some help with a Set Analysis Expression.
I have this straight table:
Customer is the dimension and expressions are:
1. Daily Sales : =SUM({$<[Date]={'$(vDateRef)'}>}[Sales]) (returns Sales for the selected date in the Input Box (variable)).
2. Date: =MAX( {$<[Date]={"<$(vDateRef)"},[Sales]={"<>0"}>} [Date]) (returns the previous (max) Date with data if there are no data for the selected date for each customer)
The problem is on the third expression, we want to see if there are no data in the selected date, what is the sales amount for the previous Date with data.
We have tryed to do something like this:
3. Date Sales:
=SUM({$<Date= {'$(=MAX({$<Date={"<$(=(vDateRef))"},Sales={"<>0"}>} Date))'} >}[Sales])
But as you can see it is only work for the previous date based on the selected date but not for every customer (if they don't have data exact 1 day before the selected).
Any help is appreciated!
Regards,
Helen
Helen,
I think you can try something like
=SUM({$<Date= {"=aggr(if(Date=max({<Date = {'<$(vDateRef)'}>}total<Customer> Date),Date),Customer,Date)"}>} [Sales])
The search expression using advanced aggregation should limit the dates to the max dates per customer < vDateRef.
Using such a search expression might somewhat limit the performance gain when using set analysis, so you could just use
=sum(aggr(if(Date=max({<Date = {'<$(vDateRef)'}>}total<Customer> Date),Sales),Customer,Date))
instead.
Hope this helps,
Stefan
Dear Stefan,
Thank you for your quick replay, I have already try these solutions, but I don't think that anyone works fine.
(1st is the first solution you suggest (with set analysis) and 2nd the second!)
You can easy understand the problem if you check the results between this 2 columns. The fist solution work fine when select one date. e.g. When we select 14/01/2010 we take the correct result 23.56, if we don't we take 33.44?????? On the other hand, at the second solution we have 0 values which is faulse.
Thanks in advance,
Helen
Not sure if I understand your last posting, are you referring to some tables or a sample app? I don't see any attachements.
I think we need to use
=sum(aggr(if(Date=max({<Date = {"<$(vDateRef)"}>}total<Customer> Date),Sales),Customer,Date,Sales))
to allow for multiple Sales entries per date.
There might be also some issues with date formatting, the variable date format must match your Date field date format.
Another problem might be the use of a set analysis search expression inside another set analysis search expression, which I intended to get around using single quotes, though this is not the correct way I believe.
Using just my above stated expression, we avoid embedding the set expressions, so this should be easier to handle.
Please find attached a small sample demonstrating the results.
Regards,
Stefan