how to get previous date selection in list box upon selection of current date in another list box

[abc_18]

I have to create two tables and data between them are associated based on posting date.

Also have two list box filters which are current date and previous date. for previous date in list box only I am using date (posting date -1)

Now requirement is like if user selects current date, previous date list box filter date should show previous date.

Please suggest how to achieve this.

[rubenmarin]

Hi, you will need another field to filter previous date, this field can be created in script, in the same table where posting date is as "Date([Posting Date]-1) as Previous Date.

If for some reason you need the same you will need to use altern staes so the selctions in one field don't affect the other but this will complicate all expressions.

[abc_18]

Hi Ruben,

Thanks for the response.

I had tried creating a new field in script as previous date.

Now the issue I am facing is that upon selection of current date , previous date values are not changing.

[rubenmarin]

Hi, something should be different, if PreviosDate is created in the same table as posting date, and it's always substracting one from the posting date, there should be only one previous date for each posting date.

Other question is the use you can give to this fields, because having a field than just shows the previous day of the posting date I'm not sure what kind of use will have.

[abc_18]

Hi Ruben,

Previous date is working now, thanks.

Posting date was just an example 🙂 actual date field is DT.date.

can you please help me with pick match function for this expression below.

Currently I am using expression in table box to get the current date based value

sum(aggr(
pick(match(measure,'XYZ','ABC','DEF'),
Sum({$<[DT.Date]={"$(=DATE(([DT.Date])))"}>} count_XYZ),
sum( {$<[DT.Date]={"$(=DATE(([DT.Date])))"}>}count_ABC),
sum({$<[DT.Date]={"$(=DATE(([DT.Date])))"}>}count_DEF))
,measure,DT.Date))

But the issue is like every time I have to select the measure to get the value in table for that particular measure but the expectation is to get an expanded value for all the measures.

expectation:'

But I am getting data in table like below only for selected measure

Can you please help me with this, in case I need to make any change in the expression.

[rubenmarin]

Hi, this has stepped up in difficulty.

First, I don't know if you are trying that set analysis iterates by date but it doesn't works that way. Before any calculation the $-expansion is evaluated, so it will have only one value, it's not calcualted row by row or by the dimension of the aggr.

And if there is more than one possible date it will return null. $(=DATE(Max([DT.Date]))) will return a date, but it will be the same for all iterations/rows.

Also I'm not sure of the logic, by the syntax it looks like any row can have more than one measure and date, and for each date and measure it sums a different field, and all them are finally summed up.

I think this is really messy and needs a rethink (or maybe a better explanation).

[abc_18]

Hi Ruben,

Yeah you are right, aggr function was not being used in right place, I have changed the expression and it's working now for Current date.

I have below expression for the current date column which is working, have to calculate previous date for another column.

=sum(if(DT.Date = aggr(nodistinct max(DT.Date),measure),qty))

I tried using below expression for previous date but it's not working , can you please help me with the correct way?

=sum(if(DT.Date = aggr(nodistinct date(max(DT.Date)-1,'MM/DD/YYYY'),measure),qty))

[rubenmarin]

Hi, maybe the aggr needs a TOTAL to ignore the current dimension date, and for this it will need to be included in a aggregation function, and then DT.Date should be added to the aggr parameters, and maybe it needs something more on the Max() function. But this is hard to follow, to give a complete answer I will need a sample app to make some tests.

Just guessing: =sum(if(DT.Date = Avg(TOTAL aggr(nodistinct date(max(DT.Date)-1,'MM/DD/YYYY'),measure)),qty))