topic Re: Problem with conditional show of row in a table in QlikView

Problem with conditional show of row in a table

Mon, 15 Jul 2013 10:15:05 GMT

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,

Re: Problem with conditional show of row in a table

jonathandienst — Mon, 15 Jul 2013 10:40:13 GMT

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

Re: Problem with conditional show of row in a table

Mon, 15 Jul 2013 13:03:48 GMT

Hi Jonathan,

I tried your solution, but it does not work

Re: Problem with conditional show of row in a table

Mon, 15 Jul 2013 13:06:04 GMT

Hi Jonhatan,

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

Re: Problem with conditional show of row in a table

Mon, 15 Jul 2013 14:54:20 GMT

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)

)

Re: Problem with conditional show of row in a table

Tue, 16 Jul 2013 10:33:35 GMT

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.

Re: Problem with conditional show of row in a table

jonathandienst — Tue, 16 Jul 2013 12:13:47 GMT

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