Set analysis expression search Vs Dollar sign expansion

[srihitha]

Hi,  I have two questions .Both the queries use the below inline data

My first question is ,I am trying to use below set analysis expressions in a visualization but, they are not working.

First expression with dollar sign expansion is working fine but it is not returning any values

Second expression with expression search has some error.

I am basically doing this to try to find difference between expression search and dollar sign expansion in set analysis

/*Sum{<Country={$(=(aggr(FirstSortedvalue(Country,Year),Country))}>Amount}
Sum{<Country={"=aggr(FirstSortedvalue(Country,Year),Country)=Canada"}>Amount}*/

Let vToday = Today();
MyTable:
Load
Year(Date) as Year,
Date#(Date,'YYYY-MM-DD') as ISO_Date,
Date(Date#(Date,'YYYY-MM-DD'),'M/D/YYYY') as US_Date,
Country, Product, Amount
Inline
[Date, Country, Product, Amount
2018-02-20, Canada, Washer, 6
2018-07-08, Germany, Anchor bolt, 10
2018-07-14, Germany, Anchor bolt, 3
2018-08-31, France, Nut, 2
2018-09-02, Czech Republic, Bolt, 1
2019-02-11, Czech Republic, Bolt, 3
2019-07-31, Czech Republic, Washer, 6
2020-03-13, France, Anchor bolt, 1
2020-07-12, Canada, Anchor bolt, 8
2021-10-15, France, Washer, 1];

Second question is I could see there is difference in output between these two.But, I don't understand the logic behind the processing of a expression search vs dollar sign expansion in set analysis.Can someone please explain

expression search:

Sum({<Year = {"=Max(Year)"}>}Amount)

Dollar sign expansion:

Sum({<Year = {$(=Max(Year))}>}Amount)

Dollar sign expansion is giving expected result. It is calculating Max(Year) for all columns .

Although the other expression search is working ,it is just calculating Max(Year) for every row .

[tresesco]

Can you explain what you are trying to achieve/expected output?

[srihitha]

I am trying to understand the difference between expression search and dollar sign expansion in set analysis

I am trying to get sum(amount) for country with least Year value group by country

Sum{<Country={$(=(aggr(FirstSortedvalue(Country,Year),Country))}>Amount}

For ex: I should get sum(amount) for 2020-07-12 date (least date by country) i.e. 8 here for Canada  and for germany 10.For rest of the rows,it must be 0 

2018-02-20, Canada, Washer, 6------- sum(Amount) =6

2020-07-12, Canada, Anchor bolt, 8---- -sum(Amount) =0

2018-07-08, Germany, Anchor bolt, 10----sum(Amount) =10
2018-07-14, Germany, Anchor bolt, 3----sum(Amount) =0

[tresesco · Accepted solution]

For your solution, set analysis wouldn't help help unless you tweak more in the script. You could simply firstsortedvalue(), like:

FirstSortedValue(Aggr(Sum(Amount), Country, US_Date), US_Date)

Expression search Vs dollar expansion

Expression search work per dimension which is Country for your case (Country={"=..."} ), while for dollar expansion the evaluation works once for the entire dimension.

I know this alone might not help you clear all doubts around this expression, because yours a bit more complicated with the use of aggr(). However, try to comprehend with the clue given above and hopefully that helps you.

[srihitha]

Thank you..I got some better understanding now