Set issues

[463]

Thanks in advance for jumping into this.  Hopefully, it is in the right forum.

I am trying to do a 'budget is up or down' type of measure with an arrow up/down icon.  Right now, it is in a pivot table but, I don't think that is important right now.

I currently have the set correct for determining the loaddate but my set for using the loaddate, looking at the previous date, and determine if the budget is higher or lower between the two dates keeps throwing the error that doesn't tell you anything.  Its not complete as I have to do it step by step.  So my code looks like this:

=if(Index(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'), Date(GetFieldSelections (SH1.LoadDate), 'YYYYMMDD')) < 9,  null(),
Sum({1<SH1.PSL_1 ={'A1'}> + 1 <SH1.LoadDate={Mid(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'),20)}))
Index(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'), Date(GetFieldSelections(SH1.LoadDate),'YYYYMMDD')) - 9, 8)>} SH1.Budget)

paraphrasing:  If my selected concatted date is less than 9 characters return null ELSE sum budget for the concatted date where the date entry is the previous date (9 positions earlier).

20260201,20260209,20260215

If I selected '20260209' it will return '20260201'  also, '20260215' will return '20260209'

Its the second part that is giving me the error:
+ 1 <SH1.LoadDate={Mid(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'),20)}))
Index(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'), Date(GetFieldSelections(SH1.LoadDate),'YYYYMMDD')) - 9, 8)>}

the formula from the 'Mid' gives me the correct answer but when put in the set, I get the generic error.

I won't go into anymore of this as I have tried a few things with no workable solution.  So I am ready to learn here.

Thanks.

[marcus_sommer]

There are some syntax-errors. For example after the {Mid ...} part are two not related brackets and then without a proper delimiting/embedding follows the index() which is followed by an unrelated -9, 8 ...

I suggest to rethink the entire approach because string-concatenating values and then picking from it certain parts hints for an unsuitable data-model and/or object-structure.

The classical approach to show the current and previous value would be:

sum({< DateNum = {"$(=max(DateNum))"}>} Value)
sum({< DateNum = {"$(=max(DateNum)-1)"}>} Value)

Important is only not include the Date as dimension within the object because the previous date doesn't belong to the current one and vice visa. It's not a matter of the set statement respectively the selection state else of the association/relationship of the data to each other.

If the dates aren't in a continuous order else with unknown gaps they could be ordered with a flag and then this flag would be queried in the set statement. 

[463]

Thanks for replying.  I am admitting that I am so new to this coding - set theory - that I don't see the relationship to what you posted to what I need. 

So the code you provided doesn't work in my situation due to it working off of the current date.  My situation is that I receive a file to load weekly, thus, I have many 'loaddate' selections that I am working from and anyone of them, singularly, can be selected. Plus, they don't have a distinct pattern of receiving this file as it has a 7 day or 6, or 8 day generation date.  That is why I am concatting and sorting all of my dates into a string, determining which date was selected, and then using that point to determine the previous date.  I don't have a 'max', 'min', or average aggregation; my user can select a middle date that could be 30 values from the max or min aggregation value.

Finally, using the GetFieldSelections function notes that the date is a dimension in my example.

Again, thanks for the help.  I will keep it in mind and maybe I will see the light shortly...

[463]

Let me give another example.  I am trying to do this in a 'Title' if it matters:

This first example works (I receive the previous date from the one currently selected.  Previous date is 20260224):
=Mid(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'), Index(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'), Date(GetFieldSelections(SH1.LoadDate),'YYYYMMDD')) - 9, 8 )

And this second example works (I receive the budget entry for the selected date '20260302':
= Sum({<SH1.LoadDate={"=20260302"}>}SH1.Budget)

Why do I receive an error when I put replace the '=20260302' in the second example with the first example? 

[marcus_sommer]

Assuming that your SH1.LoadDate is a date with the pattern of YYYYMMD the set analysis query should look like: ... SH1.LoadDate  = {'20260302'} ... The format must by comparing direct values be exactly the same like the one from the native field. Your mid() won't return a date else a string.

Beside this I believe further that there easier ways to reference to a previous date. By not continuous dates you may create a simple order-dimension table, for example:

LoadDatesWithOrder:
load distinct SH1.LoadDate, rowno() as DateOrder
resident MyTable order by SH1.LoadDate;

and then selecting any SH1.LoadDate determines also the DateOrder which controls then the adjusted selection state of the expression, like:

sum({< DateOrder = {"$(=max(DateOrder)-1)"}>} Value)

[463]

I got my original post to work while using variables.  The suggested way didn't work for my experience as the rowno was being seen as a measure and I couldn't return the measure for use. Again, little experience with this tool, there could be a way that I haven't discovered. yet.

Thanks!