Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can someone tell me the difference between these two expressions because i can't work it out 😉 BUT they give me different figures (the 2nd one gives me the correct figure). What i'm trying to get is the sales for the company regardless of a selection (in this case the sales rep). The first expression seems to take into account the reps figures somehow...
=sum({1<InvoiceDate={"=inmonth(InvoiceDate,Today(),0,7)"}>}Value)
AND
=sum({<SalesRep=>} if(inmonth(InvoiceDate,Today(),0),Value))
The data structure is very simple with a sales table (InvoiceDate) and a customer table (SalesRep)
The nested expression inmonth is over the filtered selection even though sum is over the whole universe. For example,
=sum({1<Year={"=max({1} Year)"}>} Sales)
will not change regardless of the selection, but
=sum({1<Year={"=max(Year)"}>} Sales)
will change when certain selections are made that filter the Year indirectly.
Now, it's just a matter of seeing how to apply the function inmonth over the whole universe of datos.
Pover
Thanks Pover for clearing that up... any ideas how to get inmonth({1}...)
Would something like this work (taking your example)??
=sum({1<Year={"=aggr({1} max(Year),SalesRep)"}>} Sales)
Rob,
It should be simpler than using a aggr. Taking your example again and given you have or can create an additional field in the data cloud that looks something like the following:
month(InvoiceDate) as InvoiceMonth;
the following formula should work and be completely independent of the selections you make:
=sum({1<InvoiceMonth={$(=month(today()))}>} Value)
Tell me how it goes.
Pover