Compare same values in same field and display analytic table

[jujurier]

Hello everyone,

I'm currently working on a Dashboard, with several loading sources, and several tables inside the Qlik model (I give this information because I would like to find a solution without increasing the amount of table in the model, but It may end up with load script work).

I have a table which look like this:

ID	Status	Version
1	Average	1
2	Good	1
...	...	...
1	Good	2
2	Good	2

Version refers to data upload, each new upload (weekly) adds lines with a new Version number.

I would like to create, in dashboard, a table display of all ID with a Status Evolution between the Last Version and the one selected via Filter.

With my example dataset, with "1" as Selected Version, and "2" as Last Version it would be:

ID	Status Selected Version	Status Last Version
1	Average	Good

Of course, no row for ID 2 because there was no status change for ID 2 between Version 1 and 2

I tried to build a table, and bring back IDs were there were a different value in Status field for 2 different Version, with this Dimension:

(MV is a variable with last Version, and AV is a variable the previous Version.)

=if(({<Version = {'$(AV)'}>Status}) <> ({<Version = {'$(MV)'}>Status}), ID)

It seems correct for the expression editor, but I get an Invalid Dimension display on my table.

Maybe I don't get this problem the right way, some clues? 🙂 

Thank you 

[sunny_talwar · Accepted solution]

May be try this

Aggr(
    If(Only({<Version = {'$(AV)'}>} Status) <> Only({<Version = {'$(MV)'}>} Status), ID)
, ID)

[jujurier]

Thanks a lot Sunny_talwar, I can display ID column!

I got an additional resquest :

I tried to fill my other columns, showing the status value from AV Version and MV Version, with:

AV Version Status Column

=if(Version=$(AV)',Status) 

MV Version Status Column

=if(Version=$(MV)',Status) 

It works fine for the AV Column, i get my Status values displayed, but i only get empty values for the MV column.

Is that possible that the aggr function realize an aggregation on the AV and MV values, but resulting by excluding the MV ones, preventing me from insering my status with the MV value in Version field?

Here is a screenshot, I mocked data (Version 2 is AV, and Version 3 is MV)

Thank you for your time

[sunny_talwar]

How about you just do this

AV

Only({<Version = {'$(AV)'}>} Status)

MV

Only({<Version = {'$(MV)'}>} Status) 

[jujurier]

Hi,

This isn't working 😕 but I ended up with a solution which was good enough for what I needed to show:

I created a scripted 'Last Version Table', with my most recent datas with customized fields 'LAST-[fieldname]'.

For the table display, I choosed to create two table: the first one with aggr formula(Version ; LAST-Version), and a Status column, then a second one with an 'inverted' aggr formula: (LAST-Version ; Version), and a LAST-Status field.

This allowed me to have a filter sensitive Version and Status parameters, only show ID witch have a Status evolution between the selected Version and the Last Version, and display, in appropriate tables, the Status (from selection Version), and the 'LAST-Status', from Last Version.

Thank you for your help!