Help with Expression to compare values between dimensions in a table

[jameswills]

Hi All,

I'm after some help with an expression. I have a dimension called PeriodNum (P01, P02 etc...) and another called DocNum (1, 2, 3 etc...).

PeriodNum	DocNum
P01	1
P01	2
P02	1
P02	3

Is it possible to create and expression which looks for the existence of a DocNum in the previous period and returns a Y/N i.e. If I select 'P02' from 'PerdiodNum' and 'DocNum' '1' is present in both P01 and P02 it retuns a 'Y'. If DocNum 1 is only present in P02 then it returns a N?

[VBD]

Hello , you can do that with the previous() function  and sorting your data by the docnum : 

TMP:

LOAD

    "Period num",

    DocNum

FROM [lib://DataFiles/test.xlsx]

(ooxml, embedded labels, table is Feuil1);

Test :

NoConcatenate

Load   "Period num",

DocNum,

if(previous(DocNum) = DocNum,'Y','N')as flg_docnum

Resident TMP

Order by  DocNum asc, "Period num" asc; 

Drop table TMP;

Regards,

[jameswills]

Thanks Valentin but I don;t have access to the load script.

[VBD]

Hi, you can use above(total docnum) in your expression, but the table needs to be sorted by docnum. 
Also if a user change the sort, this will no longer works.

Regards,

[Kushal_Chawda]

@jameswills  With DocNum& Period in dimension, try below expression. I have used keepchar () function as I am assuming the period value is 'P01' or 'P02'. If it is Numeric, no need to use keepchar(). I have used sortable aggr() function so that even if user changes the order, you will get correct values

=aggr(if(DocNum=above(total DocNum) and (keepchar(PeriodNum,'0123456789')-keepchar(above(total PeriodNum),'0123456789'))=1,'Y','N'),
(DocNum,(NUMERIC, ASCENDING)),(PeriodNum,(TEXT,ASCENDING)))

If your period is numeric, use NUMERIC keyword in aggr sort option for Period