Firstsortedvalue - calculation

[jerryr125]

Hi -

I am trying to calculate the total number of quantity by the last date by state.

1. I created the following variable:

vquantitylocation

=IF(LEN(Category)>0,location)

** This is created so that it removes any values from my data in which the Category is Null.

2. I create the following master measure:

firstsortedvalue(Aggr(sum(quantity),$(vquantitylocation) ,-date)

Does not work - any thoughts ?? Thanks - Jerry

[jerryr125]

Hi -  also tried :

firstsortedvalue(Aggr(sum(quantity), date ,-date)

No luck - an assistance would be appreciated - Jerry

[jerryr125]

Hi - I also tried the following :

firststortedvalue(quantity,-date)

Only works when you filter out on a specific location.

How can I get the function to work regardless of filtering on location or not ?

I also need to remove the locations which invalid :

vquantitylocation

IF(LEN(Category)>0,location)

Any thoughts would be appreciated - stuck on this one - Jerry

[tresesco]

What is your dimension in chart?

[jerryr125]

Hi tresesco -

I created a master measure to display in my tables, kpis, etc...

Master measure :

mmQuantityYTD

Expression :

firstsortedvalue(quantity,-date)

Need to remove the invalid locations thus I tried to create a variable to do so and then add it to the firstsortedvalue function:

vquantitylocation

IF(LEN(Category)>0,location)

I tried this and no luck:

firstsortedvalue(Aggr(sum(quantity),$(vquantitylocation) ,-date)

[tresesco]

Where are you trying this? In KPI , or chart?

Also, in aggr(,<dimensions>), the second parameter is expected to be field name(s) and not an expression. If you can share an app with sample data and explain the expected output w.r.t that data,  it could be easier for us to help.  

[jerryr125]

Hi again Tresesco - 

Although I cannot share the application, here is what I can trying to achieve :

It seems as if the FirstSortedValue needs to remove the Locations with the Categories that are null.

Thoughts  ? Thanks - Jerry

[tresesco]

Try like:

FirstSortedValue( {<Category={"=Len(Category)>0"}>}Aggr(Sum(quantitysold), Date, Location,Category), -Date)

[jerryr125]

Hi - thank you again for your help.

Getting very close.

This formula works if you select ONLY one location. When you select more than one location - no luck

FirstSortedValue( Aggr(Sum(quantity),[date.autoCalendar.Date], location,[Category]), -[date.autoCalendar.Date])

If I can take this formula and filter on the Categories were the LEN is >0 then we should be good...

This did not work (seems to not recognize the Category field with this syntax:

"=Len(Category)>0"

FirstSortedValue( {<Category={"=Len(Category)>0"}>}Aggr(Sum(quantitysold), Date, Location,Category), -Date)

Thoughts ? Jerry

[jerryr125]

Hi Tresesco - I sent you a private message with further information.

Thanks - Jerry