Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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 ?
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
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.
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)