Bug with IF statement?

[Newton]

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 =

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

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?

[stevejoyce]

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.

[Newton]

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]))

[stevejoyce]

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]))

[Newton]

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

[stevejoyce]

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

[Newton]

Data:

(SK_COD_DATA_CONTABILIZACAO is the date dimension key)

[Fact Table]:
load * inline [
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.

[stevejoyce]

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>)

[Newton]

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

[stevejoyce]

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]:
load *
,Date#(SK_COD_DATA_CONTABILIZACAO) as Date;
load * inline [
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:
load Distinct
[Fact Table.Date]
,MonthName([Fact Table.Date]) as MonthYear
,Year([Fact Table.Date]) as Year
resident [Fact Table]
;