Presenting The Changed Salary - New & Old Value

[Anonymous]

Dears,

I hope you are doing well,

I am now developing a new report for HR Manager and one of the his request that he needs to see all salary changes happen for all employees in certain format.

The data structure like this:

Emp Name	Amount	Start Date
Jolly	1500	2012-02-21
Jolly	1700	2016-10-01
Sergo	3000	2013-09-01
Sergo	3250	2014-07-15
Sergo	3700	2015-05-01
Sergo	4000	2016-09-20
Sara	900	2015-07-29
Sara	1000	2016-02-01
Sara	1200	2016-03-01
Jon	1600	2016-09-01

and I want to present it like this:

Emp Name	Old Amount	New Amount	Last Change
Jolly	1500	1700	2016-10-01
Sergo	3000	3250	2014-07-15
Sergo	3250	3700	2015-05-01
Sergo	3700	4000	2016-09-20
Sara	900	1000	2016-02-01
Sara	1000	1200	2016-03-01
Jon	1600	0	2016-09-01

Can anyone tell me how can I make this table.

Appreciate your support,

Thanks,

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

tabTemp:
LOAD * FROM [https://community.qlik.com/thread/238408] (html, codepage is 1252, embedded labels, table is @1);

tabEmps:
LOAD [Emp Name],
    If([Emp Name]=Previous([Emp Name]), Previous(Amount), Amount) as [Old Amount],
    If([Emp Name]=Previous([Emp Name]),          Amount ,      0) as [New Amount],   
    [Start Date] as [Last Change],
    AutoNumber([Start Date],[Emp Name]) as ChangeNo
Resident tabTemp
Order By [Emp Name], [Start Date];

DROP Table tabTemp;

Right Join (tabEmps)
LOAD [Emp Name],
    ChangeNo
Resident tabEmps
Where [Emp Name]<>Previous([Emp Name]) or ChangeNo>1
Order By [Emp Name], ChangeNo desc;

DROP Field ChangeNo;

hope this helps

regards

Marco

[johnw]

Could also do this:

Dimension 1  = Emp Name

Expression 1 = above(Amount)

Dimension 2  = Amount
Expression 2 = if(above(Amount),only([Start Date]))

But I usually don't like relying on chart inter-record functions because I don't usually like how they behave when I start making selections. I usually prefer modifying the data structures if I find myself doing chart inter-record functions.

Also, it doesn't give you the Jon line, but the Jon line makes no sense to me - Jon's salary did not go from 1600 to 0 on 2016-09-01. It went from 0 to 1600. But no other employees are showing a transition from 0 to their initial salary. So it just doesn't seem like Jon belongs in the chart.

See attached. Borrowed Marco's example.

[Anonymous]

Hi Marco,

Thank you very much for your help, it is exactly what I want.

Thanks,

Ibrahim

[Anonymous]

Hi John,

I fully agree with you and that is my intention to not show Jon's case because they don't have any salary changes and it doesn't make sense to present them in this report.

Thank you for your remarks.

Thanks,

Ibrahim

[MarcoWedel]

you're welcome

Glad it helped

regards

Marco