Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
itdepartment
Contributor II
Contributor II

How can I detect changes in dimension's value?

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:

itdepartment_2-1618417747975.png

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

itdepartment_0-1618417106410.png

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:

itdepartment_1-1618417392533.png

 

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

 

Thanks in advance!

4 Replies
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:

MayilVahanan_0-1618475555764.png

 

Thanks & Regards,
Mayil Vahanan R
itdepartment
Contributor II
Contributor II
Author

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:

itdepartment_0-1618480814097.png

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)

MayilVahanan_0-1618483038433.png

 

Thanks & Regards,
Mayil Vahanan R
itdepartment
Contributor II
Contributor II
Author

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