If Statement

[mparker123]

Hello Everyone,

General question: How do you incorporate ((epic_cf_estimated_aum)  = (a billion)) into my equation. Below is what i have tried to get it to work. I am trying to build a dynamic text box so I am going to eventually set epic_cf_estimated_aum equal to (a thousand, a million, and a trillion).

Money(if(epic_cf_estimated_aum = (a billion) and epic_issue_type='Epic' and project_name<>'PMO Test' and project_name<>'PMO IMS Pipeline' and epic_status<>'Not Required' and epic_status<>'Done', Sum(epic_cf_estimated_aum), '$ #,##')/1000000000 & 'B',

[Miguel_Angel_Baeyens]

Unless you have only one value possible in the field epic_cf_estimated_aum based on your selections, I would use some aggregation function like Sum() or even Only() like

Money(If(Sum(epic_cf_estimated_aum) = 1000000000 AND epic_issue_type = 'Epic' AND project_name <> 'PMO Test' AND project_name <> 'PMO IMS Pipeline' AND epic_status <> 'Not Required' AND epic_status <> 'Done', Sum(epic_cf_estimated_aum), '$ #,##') / 1000000000 & 'B'

Otherwise there will be multiple values possible for epic_cf_estimated_aum and the condition will never be met. Additionally, must the value be identical, and not greater than or equal to 1000000000 ?

You may want to add a set analysis right after the Sum( to further narrow down the query, or give a try at:

Money(

Sum({< epic_issue_type = {'Epic'}, project_name -= {'PMO Test','PMO IMS Pipeline'}, epic_status -= {'Note Required','Done'} epic_cf_estimated_aum = {">=1000000000"} >} epic_cf_estimated_aum)

, '$#,##') / 1000000000 & 'B'

[mparker123]

it would be a range. So >=1,000,000,000 and <=999,999,999,999 for billions.

[Miguel_Angel_Baeyens]

Then replace in the first expression

Money(If(Sum(epic_cf_estimated_aum) = 1000000000

by

Money(If(Sum(epic_cf_estimated_aum) >= 1000000000 AND Sum(epic_cf_estimated_aum) <= 9999999999

And similarly in the second expression

epic_cf_estimated_aum = {">=1000000000"}

by

, epic_cf_estimated_aum = {">=1000000000<=9999999999"}

(missing comma before the name of the field)

[mparker123]

The equations don't seem to work. Am I suppose to use both the if statement and the set analysis in the expression or just one or the other?

[Miguel_Angel_Baeyens]

Either or. I used the set analysis in case it simplifies (I avoid the use of If() statements in expressions unless it is technically not possible otherwise).

The syntax of your expression is correct, but depending on your datamodel, other expressions could work better, for example:

Sum(If(epic_cf_estimated_aum >= 1000000000 AND epic_cf_estimated_aum <= 9999999999, epic_cf_estimated_aum))

Anyway, start with a simpler version of the expression, like the line above, and add more conditions once you see it works.

Perhaps if you upload an example QVW with some data to play with we can give you a more accurate reply.

[mparker123]

I have attached a mocked up Qlikview and excel file. So what I would like to see is if I update AUM on the excel file to equal thousands, millions, billions, or trillions it will automatically adjust and it will display the proper symbol (K, M, B, T).

[Miguel_Angel_Baeyens]

Why don't you use the length of the value -or the value itself- as a flag for the symbol in the load script?

In charts you can do this using the Properties > Number tab.

In a text object you would need anyway to use a dynamic expression using Pick() and Match(), for example, or a nested If(), see attached.