How can I detect changes in dimension's value?

[itdepartment]

Hi community! Hope you can help me with this query.

I would like to detect when employees change their category during the year and then showing it in a bar chart.

I made this sample to try explain better:

First of all, detect all the situations by employee that I need (highlighted in green)

Albert had 2 changes during last year, on March he upgrades from Junior to Senior and on November Senior to Manager.

Becky had 1 change during last year, on September she upgrades from Junior to Senior.

Once I can identify this cases, I need to represent it in a bar chart like this:

Hope you can understand my explanation and give me some positive answers.

Thanks in advance!

[MayilVahanan]

HI @itdepartment 

Try like below

Load *, if(Emp = Previous(Emp), If(Category <> Previous(Category), 1, 0)) as Flag Inline
[
Emp, Mth, Category
AL, Jan, Junior
AL, Mar, Junior
AL, Apr, Senior,
AL, May, Senior
AL, Jun, Manager
BY, Jan, Junior
BY, Mar, Junior
BY, May, Senior
];

Front end:

[itdepartment]

Hi MayilVahanan, thanks for your answer. Your tricky is great for a basic dataset, but my script is very complex. So I need to calculate it in chart expression.

I have tried this expressions (and it works) in a straight table.

Column Changes : 

if(

aggr(Above(Category), Employee, (EndMonth,NUMERIC,ASCENDING)) <> aggr(Category,  Employee, (EndMonth,NUMERIC,ASCENDING)) and not IsNull(aggr(Above(Category), Employee, (EndMonth,NUMERIC,ASCENDING)))
,1
,0
)

Column Change to :

if(

aggr(Above(Category), Employee, (MonthEnd,NUMERIC,ASCENDING)) <> aggr(Category,  Employee, (MonthEnd,NUMERIC,ASCENDING)) and not IsNull(aggr(Above(Category), Employee, (MonthEnd,NUMERIC,ASCENDING)))
,aggr(Category, Employee, (MonthEnd,NUMERIC,ASCENDING))
,null()
)

The result in straight table object looks fine:

But now, I want show this as bar chart. If I use the expression [Changes], no data show in the bar chart.

[MayilVahanan]

HI @itdepartment 

You can write like below instead of above

Dim:

Aggr(if(Above(Category)<> Category and not IsNull(Above(Category)), Category), Employee, (EndMonth,NUMERIC,ASCENDING))

Exp: Count(Employee)

[itdepartment]

It works for the object, but with this solution (adding a calculated dimension) I can't filter by dimension "Category".