Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Bug with IF statement?

I have two pivot tables, both use the same dimension (row) and measure.
However, the first one shows values for the current year (year that is selected in the filter pane), and the second one should show values for the year before that.

So, inn the second table, I set (using the set analysis below) to use the year before the selected one and ignore the Months field. it works.

Year Field = {\$(=Max([Year Field]-1))},
Month Field =

The problem is:

I need to display each "Indicator" in a different number formatting, so I make an IF:

IF Indicator 1 = Num([Measure], '#.##0') (and so on)

and suddenly it stop works properly:

https://i.imgur.com/XEpJtUj.png

Any suggestions?

Labels (2)

• ### Qlik Sense

1 Solution

Accepted Solutions
Specialist II

Your Code isn't returning result in the context of previous year's month.  I did what I mentioned on a previous post and it's working only({1} [Fact Table.Code]) and used pick as it's a bit simpler...

pick(only({1} [Fact Table.Code]) ,

Sum({<
Year = {\$(=Max(Year-1))},
MonthYear =
>}
[Fact Table.Value])

,

Num(Sum({<
Year = {\$(=Max(Year -1))},
MonthYear =
>}
[Fact Table.Value]), '#.##0')

,

Num(Sum({<
Year = {\$(=Max(Year-1))},
MonthYear =
>}
[Fact Table.Value]), '#.##0,0000')

)

13 Replies
Specialist II

Try stripping down your if statement as their own measures in the pivot table.  It will become clear what function is returning nulls.  You'll likely need to include the same set analysis (Year Field = {\$(=Max([Year Field]-1))}) in your if statement, not just the final measure.

Contributor II
Author

Even if I use, for test purposes, only one IF,  it stops working:

If([Indicator Dimension] = 1,

Sum({<
[Year Field] = {\$(=Max([Year FIeld]-1))},
[Month Field] =
>} [Measure]))

Specialist II

Can you try this:

If(only({<
[Year Field] = {\$(=Max([Year FIeld]-1))},
[Month Field] =
>} [Indicator Dimension]) = 1,

Sum({<
[Year Field] = {\$(=Max([Year FIeld]-1))},
[Month Field] =
>} [Measure]))

or simpler...

If(only({1} [Indicator Dimension]) = 1,

Sum({<
[Year Field] = {\$(=Max([Year FIeld]-1))},
[Month Field] =
>} [Measure]))

Contributor II
Author

Nah 😕

The first one returns this:

https://i.imgur.com/6EOcoL4.png

and the second one only works for the first Indicator. If I try to change 1 to 2 then it stop working (or am I doing something wrong?)

https://i.imgur.com/7pp5WWj.png

Specialist II

Are you able to share your qvf?  Or sample inline data and your chart expressions?

Contributor II
Author

Data:

(SK_COD_DATA_CONTABILIZACAO is the date dimension key)

[Fact Table]:
SK_COD_DATA_CONTABILIZACAO, Code, Indicator, Value
20210701, 1, "Indicator 1", 5
20210601, 1, "Indicator 1", 0
20210501, 1, "Indicator 1", 2
20210401, 1, "Indicator 1", 8
20210301, 1, "Indicator 1", 0
20210201, 1, "Indicator 1", 0
20210101, 1, "Indicator 1", 10
20201201, 1, "Indicator 1", 5
20201101, 1, "Indicator 1", 1
20201001, 1, "Indicator 1", 2
20200901, 1, "Indicator 1", 8
20200801, 1, "Indicator 1", 7
20200701, 1, "Indicator 1", 16
20200601, 1, "Indicator 1", 11
20200501, 1, "Indicator 1", 8
20200401, 1, "Indicator 1", 12
20200301, 1, "Indicator 1", 7
20200201, 1, "Indicator 1", 9
20200101, 1, "Indicator 1", 1
20210701, 2, "Indicator 2", 123
20210601, 2, "Indicator 2", 100
20210501, 2, "Indicator 2", 452
20210401, 2, "Indicator 2", 232
20210301, 2, "Indicator 2", 80
20210201, 2, "Indicator 2", 80
20210101, 2, "Indicator 2", 100
20201201, 2, "Indicator 2", 135
20201101, 2, "Indicator 2", 0
20201001, 2, "Indicator 2", 98
20200901, 2, "Indicator 2", 8
20200801, 2, "Indicator 2", 455
20200701, 2, "Indicator 2", 0
20200601, 2, "Indicator 2", 144
20200501, 2, "Indicator 2", 32
20200401, 2, "Indicator 2", 120
20200301, 2, "Indicator 2", 70
20200201, 2, "Indicator 2", 880
20200101, 2, "Indicator 2", 11
20210701, 3, "Indicator 3", 111
20210601, 3, "Indicator 3", 0
20210501, 3, "Indicator 3", 200
20210401, 3, "Indicator 3", 8
20210301, 3, "Indicator 3", 0
20210201, 3, "Indicator 3", 450
20210101, 3, "Indicator 3", 10
20201201, 3, "Indicator 3", 75
20201101, 3, "Indicator 3", 40
20201001, 3, "Indicator 3", 12
20200901, 3, "Indicator 3", 80
20200801, 3, "Indicator 3", 20
20200701, 3, "Indicator 3", 10
20200601, 3, "Indicator 3", 1
20200501, 3, "Indicator 3", 5
20200401, 3, "Indicator 3", 1
20200301, 3, "Indicator 3", 99
20200201, 3, "Indicator 3", 0
20200101, 3, "Indicator 3", 450
];

Chart Expressions:

Row:

[Fact Table.Indicator]

Column:

[Data de Contabilização.Mês Abreviado Ano] (returns month and year, e.g: "JAN 2021")

Measure:

If([Fact Table.Code] = 1,

Sum({<
[Data de Contabilização.Ano] = {\$(=Max([Data de Contabilização.Ano]-1))},
[Data de Contabilização.Mês Abreviado Ano] =
>}
[Fact Table.Value]))

Ano = Year

Mês Abreviado Ano = Abbreviated Month  plus Year

I have these two fields as filter pane. To test, select 2021.

Specialist II

You don't have "else" parameters in your if-statement.  You only if have Code = 1, then display expression.  I would expect to see

If([Fact Table.Code] = 1, <exp1>, if([Fact Table.Code] = 2, <exp2>, <exp3>))

easier you can use the pick() function...

pick([Fact Table.Code] , <exp1>, <exp2>, <exp3>)

Contributor II
Author

Sent the if statement with only 1 condition because that's enough for the table to stop working. the full expression would be:

If([Fact Table.Code] = 1, Sum({<
[Data de Contabilização.Ano] = {\$(=Max([Data de Contabilização.Ano]-1))},
[Data de Contabilização.Mês Abreviado Ano] =
>}
[Fact Table.Value]),

If([Fact Table.Code] = 2, Num(Sum({<
[Data de Contabilização.Ano] = {\$(=Max([Data de Contabilização.Ano]-1))},
[Data de Contabilização.Mês Abreviado Ano] =
>}
[Fact Table.Value]), '#.##0'),

If([Fact Table.Code] = 3, Num(Sum({<
[Data de Contabilização.Ano] = {\$(=Max([Data de Contabilização.Ano]-1))},
[Data de Contabilização.Mês Abreviado Ano] =
>}
[Fact Table.Value]), '#.##0,0000')
)))

https://i.imgur.com/K7XeClq.png

The result show the totals but not month-to-month.

That would be the correct result: (but with the num formatting)

https://i.imgur.com/iaO5Ip7.png

Specialist II

I added a couple pieces to the script to handle the date formatting, added QUALIFY so that your field names in your expression matched the field names in the data model, and added monthyear/year.  Any of those your issues?  Results look good here, screenshot below.

SET DateFormat='YYYYMMDD';

Qualify * ;
[Fact Table]:
,Date#(SK_COD_DATA_CONTABILIZACAO) as Date;
SK_COD_DATA_CONTABILIZACAO, Code, Indicator, Value
20210701, 1, "Indicator 1", 5
20210601, 1, "Indicator 1", 0
20210501, 1, "Indicator 1", 2
20210401, 1, "Indicator 1", 8
20210301, 1, "Indicator 1", 0
20210201, 1, "Indicator 1", 0
20210101, 1, "Indicator 1", 10
20201201, 1, "Indicator 1", 5
20201101, 1, "Indicator 1", 1
20201001, 1, "Indicator 1", 2
20200901, 1, "Indicator 1", 8
20200801, 1, "Indicator 1", 7
20200701, 1, "Indicator 1", 16
20200601, 1, "Indicator 1", 11
20200501, 1, "Indicator 1", 8
20200401, 1, "Indicator 1", 12
20200301, 1, "Indicator 1", 7
20200201, 1, "Indicator 1", 9
20200101, 1, "Indicator 1", 1
20210701, 2, "Indicator 2", 123
20210601, 2, "Indicator 2", 100
20210501, 2, "Indicator 2", 452
20210401, 2, "Indicator 2", 232
20210301, 2, "Indicator 2", 80
20210201, 2, "Indicator 2", 80
20210101, 2, "Indicator 2", 100
20201201, 2, "Indicator 2", 135
20201101, 2, "Indicator 2", 0
20201001, 2, "Indicator 2", 98
20200901, 2, "Indicator 2", 8
20200801, 2, "Indicator 2", 455
20200701, 2, "Indicator 2", 0
20200601, 2, "Indicator 2", 144
20200501, 2, "Indicator 2", 32
20200401, 2, "Indicator 2", 120
20200301, 2, "Indicator 2", 70
20200201, 2, "Indicator 2", 880
20200101, 2, "Indicator 2", 11
20210701, 3, "Indicator 3", 111
20210601, 3, "Indicator 3", 0
20210501, 3, "Indicator 3", 200
20210401, 3, "Indicator 3", 8
20210301, 3, "Indicator 3", 0
20210201, 3, "Indicator 3", 450
20210101, 3, "Indicator 3", 10
20201201, 3, "Indicator 3", 75
20201101, 3, "Indicator 3", 40
20201001, 3, "Indicator 3", 12
20200901, 3, "Indicator 3", 80
20200801, 3, "Indicator 3", 20
20200701, 3, "Indicator 3", 10
20200601, 3, "Indicator 3", 1
20200501, 3, "Indicator 3", 5
20200401, 3, "Indicator 3", 1
20200301, 3, "Indicator 3", 99
20200201, 3, "Indicator 3", 0
20200101, 3, "Indicator 3", 450
];

UNQUALIFY *;

calendar: