Determine which expression column number has a particular value

[qlikviewrmw]

Good day

I am trying to determine from a PivotTable which expression has a particular value associated with it.

What I am wanting is to find out which column in the expressions has a ZERO value. The first occurence of the ZERO.

And then display this in my dimension. My expression totals are based on year and week so trying to figure out which year and week has the first occurence of a ZERO.

Is this even possible?

Thank you in advance

[sunny_talwar]

Would you be able to show a sample of what you have? I think seeing what you have will help us understand what you might need

[qlikviewrmw]

A rough outline. There is the parent group dimension. Then a grouping for expressions for year and week showing a value.

When a zero is returned I want to know which column it was from, or for that matter what year and week. And then display that in the "red shaded" column. Hope that makes sense and helps in assisting?

[sunny_talwar]

Something like this

First 0 is a calculated dimension like this

=Aggr(Min(Aggr(If(Sum(Amount) = 0, RowNo()), PG, Year, Week)), PG)

[qlikviewrmw]

Thank you, going to give it a try. Much appreciated!!

[qlikviewrmw]

Seems to work for some rows and then others it does not. Very strange. I will investigate and try and few different things to see if I can get this to work.

[sunny_talwar]

Are these the only dimensions in your chart or do you have more dimensions? Also, when you say it doesn't work... do you mean that the value isn't correct or just gives null or 0?

[sunny_talwar]

Since you are using Qlik Sense, try this

=Aggr(Min(Aggr(If(Sum(Amount) = 0, RowNo()), PG, (Year, (NUMERIC)), (Week, (NUMERIC)))), PG)

[qlikviewrmw]

Apologies for late reply.

When I say it does not work I mean that for some of the rows it returns the correct column that has a ZERO. Other times it returns a column that does not match, and other times it returns a - which is nothing. I have tried with your above code as well and that does not work at all, get - for every row

[sunny_talwar]

Did you get to try this?

=Aggr(Min(Aggr(If(Sum(Amount) = 0, RowNo()), PG, (Year, (NUMERIC)), (Week, (NUMERIC)))), PG)