Problem with conditional show of row in a table

[#1]

Hello,

I am experiencing the following problem with Qlikview.

I have the following table and I want to show only the row with "TipoRecord=C" when for each Mese (say Mese=10) there are both value (C and P).

While when for the field 'Mese' there is only "TipoRecord=P" , then it should show the row for P.

The rationale is that since

C= "at cost"

P= "forecast"

there is no need to show forecast (P) when I have the actual value (C).

Thank you in advance,

[jonathandienst]

Hi

Assuming that Mese is a dimension on your chart/table, then this should work:

If(Count({<TipoRecord={'C'}>} TipoRecord) > 0,

     Sum({<TipoRecord={'C'}>} Amount),

     Sum({<TipoRecord={'P'}>} Amount)

)

Hope that helps

Jonathan

[#3]

Hi Jonathan,

I tried your solution, but it does not work

[#4]

Hi Jonhatan,

I attach a qvw file, so you can see by yourself if I applied your solution in the right way.

[#5]

Hi,

in the mean time, I resolved the problem by myself and I post the solution just for future reference.

=If(aggr(count([tipo record]),mese, anno)>1,

aggr(sum({<[tipo record]={'C'}>}[costo totale]),mese, anno),

aggr(sum([costo totale]),mese, anno)

)

[#6]

Dear Jonathan,

please could you help me to understand why your solution does not work?

Actually I found that my solution works if the dimensions are fixed in the table, however in my real production table I have a table where the dimensions are mobile (I have a filter and  I can select which dimension to show and which dimension to hide).

so in that case my solution does not work, but your solution could work as it is dimensions-indipendent.

Hope I have explain the problem, but ask me if something it is not clear, thanks for your help in any case.

[jonathandienst]

Hi

I think it is working correctly, at least in the way I understood your question. In your example qvd:

     2006/01 has for 'C' a value of 330 and for 'P' a value of 278. The expression returns 330.

     2006/05 has a value of 847 for 'C', The expression returns 847.

     2009/06 has a value of 284 for 'P' and no value for 'C'. The expression returns 284.

Perhaps you confused things by leaving [tipo record] as a dimension? Or else I have not correctly understood your requirement.

Regards

Jonathan