Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IF-Statement does not show values

Dear all,

I've created an IF-Statement in a chart:

=if([cpa_Main Article Group Description]='Information Center',

    (sum(aggr((sum({<[cpa_Main Article Group Description]={'NICU Monitoring'}>}[cpa_Net Value]*CurrencyRate)/

    sum({1<[Old Product Number]={'M8105A','M8001A','M8002A','M8003A','M8004A','M8005A','M8007A','M8008A','M8010A','865240'}>}[cpa_Net Value]*CurrencyRate)*

    sum({1<[cpa_Article Group]={1574}>}[cpa_Net Value]*CurrencyRate*0.5)),[cpa_Geo 4],[cpa_Fiscal Month Name],[cpa_Fiscal Year]))),

if([cpa_Main Article Group Description]='Measurements allocated',

    (sum(aggr((sum({<[cpa_Main Article Group Description]={'NICU Monitoring'}>}[cpa_Net Value]*CurrencyRate)/

    sum({1<[Old Product Number]={'M8105A','M8001A','M8002A','M8003A','M8004A','M8005A','M8007A','M8008A','M8010A','865240'}>}[cpa_Net Value]*CurrencyRate)*

    sum({1<[cpa_Article Group]={'1582','1709'}>}[cpa_Net Value]*CurrencyRate*1.0)),[cpa_Geo 4],[cpa_Fiscal Month Name],[cpa_Fiscal Year]))),

sum([cpa_Net Value]*CurrencyRate))))

  My Dimension is Year and Article Group.

If I do not select anything it shows me a different Revenue, than selecting the ArticleGroup in the IF-Statement.

Thank you very much for your help

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

This seems to work (addition in red):

=if([cpa_Main Article Group Description]='IntelliVue Information Center',
(sum(aggr((sum({<[cpa_Main Article Group Description]={'NICU Monitoring'}>}[cpa_Net Value]*CurrencyRate)/
sum({1<[Old Product Number]={'M8105A','M8001A','M8002A','M8003A','M8004A','M8005A','M8007A','M8008A','M8010A','865240'}>}[cpa_Net Value]*CurrencyRate)*
sum({1<[cpa_Article Group]={1574}>}[cpa_Net Value]*CurrencyRate*0.5)),[cpa_Geo 4],[cpa_Fiscal Month Name],[cpa_Fiscal Year]))),

if([cpa_Main Article Group Description]='Medical Consumables and Sensors',
sum({<[cpa_Main Article Group Description]={'Medical Consumables and Sensors'}>}[cpa_Net Value]*CurrencyRate*[Perinatal Factor]),

if([cpa_Main Article Group Description]='IntelliVue Measurements allocated',
(sum({<[cpa_Main Article Group Description]={'IntelliVue Measurements allocated'}>}
aggr((sum({<[cpa_Main Article Group Description]={'NICU Monitoring'}>}[cpa_Net Value]*CurrencyRate)/
sum({1<[Old Product Number]={'M8105A','M8001A','M8002A','M8003A','M8004A','M8005A','M8007A','M8008A','M8010A','865240'}>}[cpa_Net Value]*CurrencyRate)*
sum({1<[cpa_Article Group]={'1582','1709'}>}[cpa_Net Value]*CurrencyRate*1.0)),[cpa_Geo 4],[cpa_Fiscal Month Name],[cpa_Fiscal Year]))),

sum([cpa_Net Value]*CurrencyRate))))

View solution in original post

6 Replies
Not applicable
Author

without.bmp

if I do not select any Main Article Group, it looks like this one above.

One of the Main Article Groups is missing.

If I do select this Main Article Group it is shown:

with.bmp


Where is the mistake?

Not applicable
Author

Can anybody help?

ToniKautto
Employee
Employee

Please look in the expression editor and you will see that there is a syntax error highlighted in line 7, related to usage of an unexisting field.

Not applicable
Author

Thanks for your help Toni,

do you mean this line?:

if([cpa_Main Article Group Description]='Medical Consumables and Sensors',

    sum({<[cpa_Main Article Group Description]={'Medical Consumables and Sensors'}>}[cpa_Net Value]*CurrencyRate*[Perinatal Factor]),

It's ok, to leave this statement out. I just reduced the data, so it was still stored in. But if I take this partial if-statement out, the problem still exists.

johnw
Champion III
Champion III

This seems to work (addition in red):

=if([cpa_Main Article Group Description]='IntelliVue Information Center',
(sum(aggr((sum({<[cpa_Main Article Group Description]={'NICU Monitoring'}>}[cpa_Net Value]*CurrencyRate)/
sum({1<[Old Product Number]={'M8105A','M8001A','M8002A','M8003A','M8004A','M8005A','M8007A','M8008A','M8010A','865240'}>}[cpa_Net Value]*CurrencyRate)*
sum({1<[cpa_Article Group]={1574}>}[cpa_Net Value]*CurrencyRate*0.5)),[cpa_Geo 4],[cpa_Fiscal Month Name],[cpa_Fiscal Year]))),

if([cpa_Main Article Group Description]='Medical Consumables and Sensors',
sum({<[cpa_Main Article Group Description]={'Medical Consumables and Sensors'}>}[cpa_Net Value]*CurrencyRate*[Perinatal Factor]),

if([cpa_Main Article Group Description]='IntelliVue Measurements allocated',
(sum({<[cpa_Main Article Group Description]={'IntelliVue Measurements allocated'}>}
aggr((sum({<[cpa_Main Article Group Description]={'NICU Monitoring'}>}[cpa_Net Value]*CurrencyRate)/
sum({1<[Old Product Number]={'M8105A','M8001A','M8002A','M8003A','M8004A','M8005A','M8007A','M8008A','M8010A','865240'}>}[cpa_Net Value]*CurrencyRate)*
sum({1<[cpa_Article Group]={'1582','1709'}>}[cpa_Net Value]*CurrencyRate*1.0)),[cpa_Geo 4],[cpa_Fiscal Month Name],[cpa_Fiscal Year]))),

sum([cpa_Net Value]*CurrencyRate))))

Not applicable
Author

Many Many Thanks!  I take my hat of for your!