Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to go back and sum 30, 60 and 90 days before a service was completed, but all the numbers have been the same as an expression without the change in days. Why might this be happening and how can I fix it? All four expressions are below. Please help!!! By the way I am dimensioning on MonthYear.
sum(aggr(max({<$(vFilterVariable), Datenum={'<=$(2ndDatenum)'}>}total<ID>
FieldA), ID))
sum(aggr(max({<$(FilterVariable), MonthYear=, Datenum={'<=$(2ndDatenum)', '>=$(2ndDatenum)-30'}>}total
<ID> FieldA),ID))
sum(aggr(max({<$(FilterVariable), MonthYear=, Datenum={'<=$(2ndDatenum)', '>=$(2ndDatenum)-60'}>}total
<ID> FieldA),ID))
sum(aggr(max({<$(FilterVariable), MonthYear=, Datenum={'<=$(2ndDatenum)', '>=$(2ndDatenum)-90'}>}total
<ID> FieldA),ID))
What is 2ndDatenum? A Variable? I don't know that I'd start a variable name with a number, but that may not be an issue.
You should create a table chart with your expressions, but don't give them labels. When the chart is generated, the labels will be your expressions with your dollar sign expansions evaluated. I'm guessing 2ndDatenum is not returning the proper values, so your selections on Datenum are not being overridden (override+n?). The label trick will tell you what is being returned and should verify that.
Could you also post FilterVariable?
Just noticed something, you don't want commas in between the conditionals in your Datenum element set. just put them right next to each other (I know, it seems a little weird to do it that way). Here's #s:
sum(aggr(max({<$(FilterVariable), MonthYear=,
Datenum={'<=$(2ndDatenum)>=$(2ndDatenum)-30'}>}total
<ID> FieldA),ID))
Thanks for the quick response!!!
2ndDatenum is not what the field is actually called I just changed the name. Pretty much it is one Date field that is in number form(Datenum) which I am relating to another Date field in number form (2ndDatenum). I tried the suggestion at the end and it gave me the same value as before. For some reason it seems that the 2nd date field is being neglected because when I look at the table the expression reads
sum(aggr(max({<,MonthYear=, Datenum={'<=>=-60'}>}total
<ID> FieldA),ID))
And I definitely know that the 2ndDatenum field is populated.
Also the filter variable is related to a button which currently should not be affecting anything since it is shut off and when I take it out of the expression I get the same answers.
The aggr, max, and total functions couldn't be causing any issues, right?
If 2ndDatenum is supposed to be a field, then you can't do that. Are you making selections in 2ndDatenum and you want those selections to be applied to Datenum? If so, then you probably need GetFieldSelections() or Concat().
Are you selecting one value or more than one value in 2ndDatenum? If you're only making one selection, then the Only() function would work for you.
The reason you are getting the dashes is that the dollar sign expansion is not returning correctly. It is returning null and null is not a valid value. From the way your expression is laid out, I'm guessing 2ndDatenum has only one value. Try this:
sum(aggr(max({<$(FilterVariable), MonthYear=,
Datenum={'<=$(=Only(2ndDatenum))>=$(=Only(2ndDatenum)-30)'}>}total
<ID> FieldA),ID))
My guess is that after your put that into a label-less expression, the first one will return the selected date, the second one will return a number. Is that what you are getting?