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: 
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)