Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I really hope someone can help me with this one. Any help is welcome.
Context:
Sales representatives do registrations when they visit the points of sales (stores).
The goal:
When you pick a date, the max date is calculated. I want to use this date as a date picker.
Per customer I want to have the most recent registered value prior to this date.
QVF with clarification as attachment.
If something is not clear, please ask.
May be you need this?
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Date))"}>}Aggr(Sum(Value), Reg_Date, Customer), -Aggr(Reg_Date, Reg_Date, Customer))
or
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Max(Date)))"}>}Aggr(Sum(Value), Reg_Date, Customer), -Aggr(Reg_Date, Reg_Date, Customer))
May be you need this?
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Date))"}>}Aggr(Sum(Value), Reg_Date, Customer), -Aggr(Reg_Date, Reg_Date, Customer))
or
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Max(Date)))"}>}Aggr(Sum(Value), Reg_Date, Customer), -Aggr(Reg_Date, Reg_Date, Customer))
Can you please explain that stalwar1 ?
I've tried to answer this question yesterday but didn't find a correct way..
And honestly, I don't really understand your expression
Couple of questions before I explain
1) Do you think that the output is correct?
2) Which part of the expression you don't understand? FirstSortedValue or the set analysis or something else?
I think It's correct;
I didn't work with the firstsortedvlue expression before but I kind of know how it works.
For example,
firstsortedvalue (Value, Reg_Order) will return the date with the min Reg_Order?
In our case, the expression is:
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Max(Date)))"}>}
Aggr(Sum(Value), Reg_Date, Customer),
-Aggr(Reg_Date, Reg_Date, Customer))
How should I break that apart?
With what I know,
I would think of sthing like this:
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Max(Date)))"}>}
aggr(Sum(Value),Reg_Date,Customer)
,
aggr(Reg_Order,Reg_Date,Customer)
To return the Value (for the reg data < selected date) which has the Min reg Order by customer)
But this does not return a thing? Why?
What I am doing wrong?
Another question:
Why none of these expressions is returning correct values (when I select a date):
1)aggr(sum({ <Date=, Reg_Date=,Reg_Date={"<=$(=max(Date))"},Reg_Order={"$(=Aggr( min(Reg_Order),Customer))"} >} Value),Customer)
2)sum({<Date=,Reg_Date=,Reg_Date={"<=$(=max(Reg_Date))"},
Customer=,Reg_Order={"$(=min(Reg_Order))"}>} Value)
Second expression when I do not select a date :
Thank you for your time Sunny
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Max(Date)))"}>}
aggr(Sum(Value),Reg_Date,Customer)
,
aggr(Reg_Order,Reg_Date,Customer)
You missed a parenthesis at the end...
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Max(Date)))"}>}Aggr(Sum(Value), Reg_Date, Customer), Aggr(Reg_Date, Reg_Date, Customer))
1)aggr(sum({ <Date=, Reg_Date=,Reg_Date={"<=$(=max(Date))"},Reg_Order={"$(=Aggr( min(Reg_Order),Customer))"} >} Value),Customer)
This seems to be working for me
2)sum({<Date=,Reg_Date=,Reg_Date={"<=$(=max(Reg_Date))"},
Customer=,Reg_Order={"$(=min(Reg_Order))"}>} Value)
This worked also
Try to select a date Sunny and see if the other 2 expressions still work?
First one with date selection
Second one with date selection
No, select date in 2015 for example.
Both, thank you very much for your time and insights. I didn't even know the existence of the function FirstSortedValue.
This expression works, also with filtering on 2015.
FirstSortedValue({<Reg_Date = {"$(='<=' & Date(Max(Date)))"}>}Aggr(Sum(Value), Reg_Date, Customer), -Aggr(Reg_Date, Reg_Date, Customer))