topic Re: Previous Date Issue in App Development

Previous Date Issue

Rehan — Mon, 27 Jan 2020 20:06:09 GMT

I have a Extraction Date field but it has no sequence to it. For example I have dates like

05/1/2019

03/1/2019

2/1/2019.

I always want the Max Date and the Previous from Max Date. In this case I need 5/1/2019 and 3/1/2019. I know how to get 5/1/2019 but how will get 3/1/2019.

Re: Previous Date Issue

Vegar — Mon, 27 Jan 2020 21:25:11 GMT

Use the Max() function with two parameters like I've done in the picture below.

Re: Previous Date Issue

Rehan — Tue, 28 Jan 2020 20:07:14 GMT

HOw would you use in the set analysis for Sales

Sum({<Date={$(=Max(Date))}>} Sales)

What to write for Max(Date,2)?

Re: Previous Date Issue

Vegar — Tue, 28 Jan 2020 20:46:41 GMT

I would write it like this

=Sum({<Year={"$(=max(Year,2))"}>}Sales)

Re: Previous Date Issue

Rehan — Tue, 04 Feb 2020 03:16:10 GMT

Your solution works fine if I dont make a selection in ETL Date field.

05/1/2019

03/1/2019

2/1/2019

My expression is below which is good

Sum({<[ETL Date]={"$(=MAX([ETL Date],2))"} >} [Amount])

It gives me the data for 3/1/2019, But If I make a selection in the ETL date field everything blanks out. The requirement is that by default it is going to be 3/1/2019 but If I make a selection as 3/1/2019 this expression will give me the data for 2/1/2019

How can I accomplish that ??

Re: Previous Date Issue

Vegar — Tue, 04 Feb 2020 08:40:40 GMT

Try this instead:

Sum({<[ETL Date]={'$(=date(Max([ETL Date],2)),'DD/M/YYYY')'} >} [Amount])

Re: Previous Date Issue

Rehan — Tue, 04 Feb 2020 15:06:51 GMT

It doesn't work. I don't get anything If I add the below expression

Re: Previous Date Issue

Vegar — Tue, 04 Feb 2020 16:11:10 GMT

Could you attach a sample app or sample data ? It will make it easier to troubleshoot.

Re: Previous Date Issue

Rehan — Tue, 04 Feb 2020 17:21:59 GMT

Attached please find the sample data.. In this case by default with no selection in the extract date

I would like to calculate the amount for Max(Extract Date) and one previous date

1/25/2020 and 1/20/2020

but if the user selects 1/20/2020 the result will be

1/20/2020 and 1/15/2020

Re: Previous Date Issue

Rehan — Tue, 04 Feb 2020 18:19:26 GMT

Solved it, Created a variable vETL Date as

=Date(If(GetSelectedCount([ETL Date]) >0,
Aggr(Above(Max({<[ETL Date]>} [ETL Date])), [ETL Date]), Max({<[ETL Date]>}[ETL Date], 2)))

and used that in the expression

Re: Previous Date Issue

Vegar — Tue, 04 Feb 2020 20:57:09 GMT

The issue with my previous suggestion was that it was taking the second largest date in the selection. So if you where selecting only one date there where no second largest date in your selection. If you where to select two or more dates it will work as intended.

To meet your need I need to tell the modifier to consider all [ETL Date] smaller than the selected value. A modifier inside the modifier.

only({< [ETL Date]={"$(=MAX({<[ETL Date]= {"<=$(=MAX([ETL Date]))"} >}[ETL Date],2))"} >} [ETL Date])

Re: Previous Date Issue

Rehan — Mon, 10 Feb 2020 21:57:32 GMT

I am currently using the below expression to get the previous date. It works fine if I make a selection in ETL Date Field.

Date(If(GetSelectedCount([ETL Date]) >0,
Aggr(Above(Max({<[ETL Date]>} [ETL Date])), [ETL Date]),
Max({<[ETL Date]>}[ETL Date], 2)))

I Created a field called ETL Month from ETL Date . ETL Month is in the format "May 2019". If I select the ETL Month this expression doesnt work . I modified teh expression as

Date(If(GetSelectedCount([ETL Month]) >0,
Aggr(Above(Max({<[ETL Date]>} [ETL Date])), [ETL Date]),
Max({<[ETL Date]>}[ETL Date], 2)))

Any idea what am I doing wrong here ?

Re: Previous Date Issue

Vegar — Mon, 10 Feb 2020 22:07:51 GMT

Try to expand your set modifiers with the new month fiels. Like this.

{<[ETL Date], [ETL Month]>}

Re: Previous Date Issue

Rehan — Mon, 10 Feb 2020 22:11:06 GMT

Tried it doesn't work, Its not changing the values its staying at the Previous month value no matter what I select in ETL Month

Re: Previous Date Issue

Rehan — Mon, 10 Feb 2020 22:33:04 GMT

I guess I Spoke too soon, Its working in a variable, but when I use that variable in SET analysis , it does not work. Means by default u see previous ETL month but when you make a selection in ETL month it blanks out

Re: Previous Date Issue

Vegar — Tue, 11 Feb 2020 08:19:11 GMT

Try this:

Date(If(GetSelectedCount([ETL Month])>0,
max(Aggr(Above(Max({<[ETL Date], [ETL Month]>} [ETL Date])), [ETL Date])),
Max({<[ETL Date], [ETL Month]>}[ETL Date], 2)))