Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
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.
Can you explain what you are trying to achieve/expected output?
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
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.
Thank you..I got some better understanding now