Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
davymichiels
Contributor
Contributor

How to mark all dimensions with same value if one of them has certain value in expression

Hi there,

I am looking for a way to 'mark' all identical dimension values if a certain expression has a negative outcome for at least one of them.

Please take a look at the excel file -> I am looking for a way to mark all dimension values 'A' because for one of them there is a negative value.

Maybe important to know is that the expression (for what we are looking for negative values) is an if-expression  combined with an above() function

thanks

Davy

3 Replies
kuba_michalik
Partner - Specialist
Partner - Specialist

You can use aggr for that - something like

Min(TOTAL<ARTICLE> Aggr(Sum(STOCK) < 0, ARTICLE, DATE))

It will return -1 for every ARTICLE for which stock was <0 no matter for which month, you can put it in color expression for dimension as a condition to turn on some specific color.

I'm not sure what would be the interplay with Above() formula (which would go where I put "Sum(STOCK)"), but it's worth trying out.

davymichiels
Contributor
Contributor
Author

Hi Jakub

I’ve tried your solution but it just gives me nothing (empty values), not even a calculation error.

Even a simple min(aggr()) function to determine the lowest value for an article gives an ‘empty’…

Must have something to do with the above() function and/or with sorting of my table. The sorting in a specific way is what makes my table useful because only then the above() function gives me the wanted results. And apparently I can not detect the lowest value for each article…

Any other ideas?

Davy

Van: Jakub Michalik

Verzonden: vrijdag 2 december 2016 16:50

Aan: Davy Michiels

Onderwerp: Re: - How to mark all dimensions with same value if one of them has certain value in expression

kuba_michalik
Partner - Specialist
Partner - Specialist

I created a simple example based on the Excel data you have attached in first post - I have achieved what you want in principle, but probably your real use case is more complicated, so I don't know if you will be able to make use of it. But feel free to check the attached qvw file, and maybe it will point you in the right direction.

I had to keep in mind two things - first, aggr() produces "virtual" table that has dimensions always sorted in their load order, and second, above() operates within a column segment (here, column segment is defined by Article dimension). And it assumes there will be no more than 1000 rows for a single Article (because I use three argument version of Above, and you can't specify "whole column segment" with it, it has to be a number)