Flag Metric when prices chage, QLIK SENSE

Question

I need to create a flag metric in qliksense front (set analysis). This metric needs to mark with 1 when it detects a change in prices and 0 if its the same. The agregation (the line to wactch) is Laboratory, Product, Presentation. These are not in the same table but are related to each other in a "relation" table

Having this data:

Product A, Pres. A, Price 300 (Flag must be 0)

Product A, Pres. A, Price 350 (Flag must be 1)

Product A, pres. A, Price 250 (Flag must be 1)

Product A, pres. A, Price 250 (Flag must be 0) an so on...

The chart im going to create is something like this:

Product |Times it change | Last date of change
A | 2 | 27/10/2023

THANKS!!!!

Answer 1

Added a few more rows to your data set to show how it could work. This script has some hard-coded data and then loads it in sorted and looks up to the previous row to see if it's a change.

Source:
Load * Inline [
    Date,       Product,    Pres,   Price
    27/10/2023, A,          A,      300 
    28/10/2023, A,          A,      350 
    29/10/2023, A,          A,      250 
    30/10/2023, A,          A,      250 
    27/10/2023, B,          A,      100
    28/10/2023, B,          A,      100
    29/10/2023, B,          A,      150
];



Output:
Load *,
    if(peek(Product)=Product and peek(Pres)=Pres and peek(Price)<>Price,1,0) as PriceChangeFlag
;
Load * Resident Source
Order by Product asc, Pres asc, Date asc;

drop table Source;

That then gives an Output table like this:

Date        Product Pres    Price   PriceChangeFlag
27/10/2023  B       A       100     0
27/10/2023  A       A       300     0
28/10/2023  B       A       100     0
28/10/2023  A       A       350     1
29/10/2023  B       A       150     1
29/10/2023  A       A       250     1
30/10/2023  A       A       250     0

Which can then be used to show in the front end like this:

Product Pres    Sum(PriceChangeFlag)    date(max({<PriceChangeFlag={1}>}Date))
A       A       2                       29/10/2023
B       A       1                       29/10/2023

Hope that helps!

I wasn't sure what you meant by the aggregation line being in another table. If you meant that Product & Pres in the example above wouldn't be in the same table then I'd recommend mapping or joining that in first before doing this.

Answer 2

I got your question that you were looking for a frontend solution. If you don't want to or can't change your data model you could do this:

Create data

Data:
Load Product, Price, Date#(Date) as Date
Inline [
Product,  Price, Date
Product A, 300, 01.12.2023 
Product A, 350, 02.12.2023
Product A, 250, 03.12.2023
Product A, 250, 04.12.2023
Product B, 300, 01.12.2023
Product B, 300, 02.12.2023
Product B, 300, 03.12.2023
Product B, 350, 04.12.2023
];

Then in frontend, create a table with two measures:

Times it changed

Sum(Aggr(Sum(If(Aggr(Alt(Above(Price), Price) - Price, Product, Date) <> 0, 1, 0)), Product))

Last Date of Change

Aggr(Date(Max(If(Aggr(Alt(Above(Price), Price) - Price, Product, Date) <> 0, Date))), Product)

Which will give you this result: