topic Re: Firstsortedvalue - calculation in App Development

Firstsortedvalue - calculation

jerryr125 — Tue, 27 Apr 2021 11:03:19 GMT

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

Re: Firstsortedvalue - calculation

jerryr125 — Tue, 27 Apr 2021 16:21:45 GMT

Hi - also tried :

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

No luck - an assistance would be appreciated - Jerry

Re: Firstsortedvalue - calculation

jerryr125 — Wed, 28 Apr 2021 08:32:57 GMT

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

Re: Firstsortedvalue - calculation

tresB — Wed, 28 Apr 2021 08:37:01 GMT

What is your dimension in chart?

Re: Firstsortedvalue - calculation

jerryr125 — Wed, 28 Apr 2021 08:54:11 GMT

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)

Re: Firstsortedvalue - calculation

tresB — Wed, 28 Apr 2021 09:04:48 GMT

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.

Re: Firstsortedvalue - calculation

jerryr125 — Wed, 28 Apr 2021 11:45:50 GMT

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

Re: Firstsortedvalue - calculation

tresB — Wed, 28 Apr 2021 12:09:50 GMT

Try like:

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

Re: Firstsortedvalue - calculation

jerryr125 — Wed, 28 Apr 2021 12:46:13 GMT

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

Re: Firstsortedvalue - calculation

jerryr125 — Thu, 29 Apr 2021 02:19:10 GMT

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

Thanks - Jerry

Re: Firstsortedvalue - calculation

tresB — Thu, 29 Apr 2021 07:25:12 GMT

I got your message and app. It seems the main issue is - granularity, at level you are trying to aggregate the measures. Instead of If(Sum(... you should try with Sum(If.. . I have made changes to first two KPIs you have in your app.

Try like:

Sum(If(LEN(POPESTIMATE2020)>0,POPESTIMATE2020))

Sum({<[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'}>}people_vaccinated)

Re: Firstsortedvalue - calculation

jerryr125 — Thu, 29 Apr 2021 08:12:21 GMT

Hi - Thank you again so much for your help.

Almost there - I need to check to see if the state code has text (characters).

I tried this:

Sum({<[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'},istext(left[State Code],1)>}people_vaccinated)

No luck - thoughts ?

Re: Firstsortedvalue - calculation

jerryr125 — Thu, 29 Apr 2021 12:06:36 GMT

Ok - still trying to make progress on this - almost there.

I changed the master measure to the following:

IF((POPESTIMATE2020)>0,SUM({<[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'}>}people_fully_vaccinated),NUM('0'))

As a result:

This works

Notice : American Samoa column for 'Fully Vaccinated YTD' is 0 - which is correct.

BUT now when go to do view the KPI:

The KPI for Fully Vaccinated (YTD) it displays zero.

If I filter on one state it works.

Any thoughts ? Jerry

Re: Firstsortedvalue - calculation

tresB — Thu, 29 Apr 2021 12:43:16 GMT

That is because, you are making the same mistake, using if(sum( .... structure instead of sum(if(... (I suggested above).

Expression to be tried:

SUM({<POPESTIMATE2020={">0"},[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'}>}people_fully_vaccinated)

Please close the thread by liking and/or marking correct answer. That would help others as well.

Re: Firstsortedvalue - calculation

jerryr125 — Thu, 29 Apr 2021 13:15:21 GMT

Thank you again so much for your help and dedication

Yes, this is correct :

SUM({<POPESTIMATE2020={">0"},[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'}>}people_fully_vaccinated)