Getting 2nd and 3rd values

[Saritha077]

Hi All,
I have data like below

I want get maximum month, previous maximum month and previous to previous month example like below
For name HGI, months like below
maximum month = Jun 2023, previous maximum month = 6/8/2022, previous to previous month = 5/29/1999

I am creating a table with Name, reporting month as Aggr(Max(reporting month), Name) and expressions like below are 
1st expression :  

(Sum({<reporting_month={"$(vMaxmonth)"}>}Amount) - Sum({<reporting_month={"$(vPrevMonth)"}>}Amount)) /

Sum({<reporting_month={"$(vPrevMonth)"}>}Amount)

2nd expression : 

(Sum({<reporting_month={"$(vPrevMonth)"}>}Amount) - Sum({<reporting_month={"$(vPrev3rdvalue)"}>}Amount)) /

Sum({<reporting_month={"$(vPrev3rdvalue)"}>}Amount)

Variable are like below 
 

See below able

For HGI it is showing 840369, same way in 2nd expression it is suppose to show 8051165775.05686 but is showing empty. seems have an issue with expression while fetching 2nd previous record.

But it is not  working as expected , please help me someone to achieve this
thanks in advance

Regards,

Saritha

[diegozecchini]

Hi
it seems you are trying to calculate the maximum, previous maximum, and previous-to-previous month for a specific field (reporting_month) for each Name. You are also calculating ratios for the amounts across these months.
we need to ensure your variables vMaxmonth, vPrevMonth, and vPrev3rdvalue are correctly defined to dynamically calculate the respective maximum months based on the Name and reporting_month.

For example:

vMaxmonth:
=Date(Max(reporting_month), 'MMM YYYY')

vPrevMonth:
=Date(Aggr(Max({<reporting_month={"<$(=Max(reporting_month))"}>} reporting_month), Name), 'MMM YYYY')

vPrev3rdvalue:
=Date(Aggr(Max({<reporting_month={"<$(=Max({<reporting_month={"<$(=Max(reporting_month))"}>} reporting_month))"}>} reporting_month), Name), 'MMM YYYY')

Then use Aggr() to ensure calculations respect the level of aggregation by Name.

Update your expressions as follows:
1st Expression:
(Sum({<reporting_month={"$(vMaxmonth)"}>} Amount) - Sum({<reporting_month={"$(vPrevMonth)"}>} Amount)) /
Sum({<reporting_month={"$(vPrevMonth)"}>} Amount)

2nd Expression:
(Sum({<reporting_month={"$(vPrevMonth)"}>} Amount) - Sum({<reporting_month={"$(vPrev3rdvalue)"}>} Amount)) /
Sum({<reporting_month={"$(vPrev3rdvalue)"}>} Amount)

Finally let's validate your variables by creating text boxes in Qlik to confirm they are returning the correct dates.

=$(vMaxmonth)
Check if vPrevMonth and vPrev3rdvalue return accurate results by cross-referencing with your data.

ensure your reporting_month field is in a proper date format. If it is stored as text, you need to convert it:

Date(Date#(reporting_month, 'MMM YYYY'), 'MMM YYYY')

[Saritha077]

Hi diegozecchin,

Thanks for your reply but it is not working as expected.

Regards,

Saritha.

[Muthukumar_77]

Try this,

1st Expression:

(Sum(Aggr(FirstSortedValue(Amount,-reporting_month),Name))/Sum(Aggr(FirstSortedValue(Amount,-reporting_month,2),Name)))/
Sum(Aggr(FirstSortedValue(Amount,-reporting_month,2),Name))

2nd Expression:

(Sum(Aggr(FirstSortedValue(Amount,-reporting_month,2),Name))-Sum(Aggr(FirstSortedValue(Amount,-reporting_month,3),Name)))/
Sum(Aggr(FirstSortedValue(Amount,-reporting_month,3),Name))

[diegozecchini]

I am sorry to hear that, if you want to share some tries and output me or the community could try to help.

kind regards,

Diego