Urgent: Calling out Only the most recent value greater than 0 for each row

[jboy]

Dear All,

Please help!

I have a table containing different items in a row and each Items have values for 3 Periods. I want to create a column that gives only the most recent value that is greater than 0.

just as an example, if Period A = 0, then show value for Period B, if Period B = 0 and Period A = 0 then show value for Period C.

Thanks in Advance

[nisha_rai]

Hi,

Can you share the sample data.

[sagarjagga]

You can use RangeMax Function, Like-

RangeMax(Period A Value, Period B Value, Period C value).

[jboy]

As you can see below, the data in the column (Most Recent Cost (€) contain the most recent cost across the four periods.

Please assist.

Item	Mar-19 Cost (€)	Apr-19 (€)	Sept-19 (€)	Mar-20 (€)	Most recent Cost (€)
A001	300	340	0	0	340
A002	0	180	300	325	325
A003	0	0	250	0	250
A004	0	560	590	595	595
A005	0	0	330	0	330
A006	730	0	745	750	750

[jboy]

Thank you. The rangemax function does not apply here because the most recent balue may not necessarily be the highest value.

[sagarjagga]

if your number of columns are not giving to be changed in qliksense they you can use like

If(Column(4)>0,column(4),if(column(3)>0,Column(3),if(column(2)>0,column(2),column(1)))).

This is not the best solution. in best way , you can use crosstable in data model and use the first sorted non zero value.

[nisha_rai]

Hi,

Try the below code, may it helps to resolve your issue.

MR1:
CrossTable (MRDate,Value,1)
Load * Inline [
Item, 3/1/2019, 4/1/2019 , 9/1/2019, 3/1/2020
A001, 300 ,340, 0 ,0
A002, 0, 180, 300, 325
A003, 0, 0, 250, 0
A004, 0, 560 ,590, 595
A005, 0, 0, 330, 0
A006, 730 ,0 ,745, 750
];

//NoConcatenate
MR:
Load Item,

LastValue(Value) as MostRecent
Resident MR1
where Value<>0
group by Item
;

//Drop Table MR1;

exit Script;