Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Newton
Contributor II
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 =

 

bug1.PNG

 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:

bug2.PNG

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

 

Any suggestions?

 

Labels (2)
1 Solution

Accepted Solutions
stevejoyce
Specialist II
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')

)

 

stevejoyce_0-1630346779676.png

 

View solution in original post

12 Replies
stevejoyce
Specialist II
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.

Newton
Contributor II
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]))

stevejoyce
Specialist II
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]))

Newton
Contributor II
Contributor II
Author

Nah 😕

The first one returns this:

Newton_0-1630337055335.png

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

 

Newton_3-1630337526434.png

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

 

stevejoyce
Specialist II
Specialist II

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

Newton
Contributor II
Contributor II
Author

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

Newton
Contributor II
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')
)))

 

Newton_0-1630342717035.png

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)

Newton_1-1630342913677.png

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

stevejoyce
Specialist II
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]:
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]
;

 

stevejoyce_1-1630343637915.png

stevejoyce_2-1630343651635.png