topic Re: If Statement in QlikView

If Statement

mparker123 — Wed, 25 Nov 2020 16:16:04 GMT

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',

Re: If Statement

Miguel_Angel_Baeyens — Tue, 31 Oct 2017 13:49:13 GMT

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'

Re: If Statement

mparker123 — Tue, 31 Oct 2017 14:13:03 GMT

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

Re: If Statement

Miguel_Angel_Baeyens — Tue, 31 Oct 2017 14:31:09 GMT

Then replace in the first expression

Money(If(Sum(epic_cf_estimated_aum) = 1000000000

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"}

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

(missing comma before the name of the field)

Re: If Statement

mparker123 — Tue, 31 Oct 2017 15:14:21 GMT

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?

Re: If Statement

Miguel_Angel_Baeyens — Tue, 31 Oct 2017 15:25:40 GMT

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.

Re: If Statement

mparker123 — Tue, 31 Oct 2017 16:36:21 GMT

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

Re: If Statement

Miguel_Angel_Baeyens — Tue, 31 Oct 2017 17:30:52 GMT

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.