Fetch values for Max date and Min date using peek by ID

[Rsaiq]

Hi All,

I am trying to fetch the current year value and last year value by Id and date and storing it in another field using peek .

I am successfully getting the result for Current Year as I am fetching the value which are present for max year but same thing is not working with previous year.Below are my dataset, script  and current output.

Dataset:

Id Date Amount

1 01-01-2022 100

1 01-01-2023 200

2 01-01-2022 400

2 01-01-2023 300

Script:

Test:

Load ID,

           Date,

           Amount

From xyz;

New:

Load *,

If(ID=peek('ID'),peek('Amount')) as Current_Year_Amount,

If(ID=peek('ID'),RangeMin(peek('Amount'),Amount)) as Previous_Year_Amount

Resident Test order by ID,Date desc;

Drop table Test;

Output:

Could anyone please help me to get this done .

Thanks

[MayilVahanan]

Hi

You can use set analysis to achieve this one in front end. 

However, if you want to achieve in back end, try like below

Data:
LOAD *, If(Year(Today()) = Year(Date), Amount) as CurrentYearAmount, if(Year(Today())-1 = Year(Date), Amount) as PrevYearAmount INLINE [
Id Date Amount

1 01-01-2022 100

1 01-01-2023 200

2 01-01-2022 400

2 01-01-2023 300
](delimiter is ' ');

[Rsaiq]

Hi @MayilVahanan ,

Thanks for your reply.

Could you please help me for set analysis expression for front end.

Thanks

[MayilVahanan]

Hi

You can bring Year field in the front end. 

Data:
LOAD *,Year(Date) as Year INLINE [
Id Date Amount

1 01-01-2022 100

1 01-01-2023 200

2 01-01-2022 400

2 01-01-2023 300
](delimiter is ' ');

Current Year: Sum({<Year={$(=Max(Year))}>}Amount)

Previous Year: Sum({<Year={$(=Max(Year)-1)}>}Amount)

[Rsaiq]

Hi,

Backed script is working fine which you provide on max year but problem is like it will work only when we have id with current year but it will not give result if don't have the max year for ID let me modify the dataset

Dataset:

Id Date Amount

1 01-01-2020 100

1 01-01-2021 200

2 01-01-2020 400

2 01-01-2021 300

Now we don't have the current year in this data set.It will not give result for Previous Year.

Thanks

[MayilVahanan]

Hi

Front end will satisfy all your requirements.

For Back end, in that case, you need to find the Year of max(date) and then use in the if condition. 

Its depends on ur requirement and data. 

Data:
LOAD *,Year(Date) as Year INLINE [
Id Date Amount

1 01-01-2020 100

1 01-01-2021 200

2 01-01-2020 400

2 01-01-2021 300
](delimiter is ' ');

MaxData:
Load Max(Date) as MaxDate Resident Data;

Let vMaxYear = Year(Peek('MaxDate'));

Load *, If(Year(Date) = $(vMaxYear), Amount) as CurrentYear, if(Year(Date) = $(vMaxYear)-1, Amount) as PrevYear Resident Data;

DROP Table Data, MaxData;

[Rsaiq]

Hi @MayilVahanan ,

Thanks for your quick replies !!!

I just tried your script but unfortunately it's not working which I am looking for

Sorry maybe  I have not provided the correct requirement.I am trying to explain more in detail here(included future date into dataset to support my requirement)

Dataset:

Id Date Amount

1 01-01-2022 100

1 01-01-2023 200

2 01-01-2022 400

2 01-01-2023 300

3 01-01-2023 600

3 01-01-2024 500

I will have previous years, current year or maybe future year in my dataset.so need to take the value by checking max date for a id and min date for a id .I am correctly getting value for Current year amount with my code(please see below) but not able to get the values for Previous year amount(for a id).

And also I need current year and previous year on minimum date's row for all id.

I am sharing screenshot for Current output which I followed your script along with expected result.

 

Earlier,I  achieved what i am looking for the Current Year Amount with below script(not getting for previous)

New:

Load *,

If(ID=peek('ID'),peek('Amount')) as Current_Year_Amount,

If(ID=peek('ID'),RangeMin(peek('Amount'),Amount)) as Previous_Year_Amount

Resident Test order by ID,Date desc;

Thanks in advance.

[Rsaiq · Accepted solution]

Hi @MayilVahanan ,

I have achieved what I was looking for the Previous_Year_Amount.I did modify the script using rangemax.Thanks for your replies.

RangeMax(if(ID=previous(ID),Amount)) as Previous_Year_Amount