if condition in pivot table expression

[Peony]

Hi there!

I've got misunderstanding with qlik's expressions in pivot table.

Have a table with 3 fields: Date (contains date, no null values) , Category (contains names of differen data category, no null values for category 'Sales'), Digit (contains digits and some null value).

Whan I create pivot table, set as dimention Date and than Category. And the expression is: if (IsNull(Digit),'No data', Digit). But as the result, I have short table with only 'No data' values and withour other "not null values".

What am I doing wrong in this case?

[jonathandienst · Accepted solution]

Probably because you have more than one value of Digit in those cells. [Digit], as a naked field*, will return Null if there is more than one possible value in the pivot table cell. So that expression will not work quite that way. You may need to add Digit as a dimension as well.

* A naked field is a field reference not enclosed in an aggregation function (Sum(), Avg(), Min() etc). IsNull is not an aggregation function, so Digit is a naked field. See here for more Use Aggregation Functions!

[arvio124]

Hi,

i think you have more than on result per line. it should work with:

      if(isnull(sum(Digit)),'No Data',sum(Digit))

[Peony]

Yes! There is the mistake. You right! Thank you much!

[Peony]

Hi. It works! Thank you for your idea.