Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jujurier
Contributor II
Contributor II

Compare same values in same field and display analytic table

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:

IDStatusVersion
1Average1
2Good1
.........
1Good2
2Good2

 

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:

IDStatus Selected VersionStatus Last Version
1AverageGood

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 

Labels (5)
1 Solution

Accepted Solutions
sunny_talwar

May be try this

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

View solution in original post

4 Replies
sunny_talwar

May be try this

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

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)

table.png

 

Thank you for your time

sunny_talwar

How about you just do this

AV

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

 

MV

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

 

jujurier
Contributor II
Contributor II
Author

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!