topic Re: Dynamic Totaling in QlikView

Dynamic Totaling

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

Hello Everyone,

I have a Text Object with the following expression in it:

= 'Total AUM' & '

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

The text box is totaling epic_cf_estimated_aum, I would like the text box to be dynamic in the sense that if the AUM falls in thousands, millions, billions, or trillions the B on the end of the equation will change to K, M, or T. Right now I hard coded the equation to display the B i want it to change as the number either goes up or down.

If someone can let me know if this is at all possible? If so, can someone help me create a new expression or maybe it is just an add on to the one above?

Re: Dynamic Totaling

Anil_Babu_Samineni — Thu, 19 Oct 2017 14:51:01 GMT

Unfortunately is this not implemented in that way..

num(Expression / 1000, '#.##0 k') for thousands

num(Expression / 1000000, '#.##0,0 m') for millions

Re: Dynamic Totaling

devarasu07 — Thu, 19 Oct 2017 14:59:27 GMT

Hi,

Can you try like this?

Number formatting - millions to billions

Re: Dynamic Totaling

lorenzoconforti — Thu, 19 Oct 2017 21:41:54 GMT

To make the code in your text box leaner you could store the expression in a variable

vTotalAUM =Money(Sum(If(epic_issue_type='Epic' and project_name<>'PMO Test' and project_name<>'PMO IMS Pipeline' and epic_status<>'Not Required' and epic_status<>'Done', epic_cf_estimated_aum)), '$ #,##')

Then in your text box you could write something like:

= 'Total AUM ' & if ($(vTotalAUM) > 1000000000 , $(vTotalAUM)/1000000000 & ' B',if ($(vTotalAUM) > 1000000 , $(vTotalAUM)/1000000 & ' M',if ($(vTotalAUM) > 1000, $(vTotalAUM)/1000 & ' K')))

Re: Dynamic Totaling

mparker123 — Fri, 20 Oct 2017 13:58:36 GMT

Lorenzo,

Thank you for the post. But have you had success with the formula you suggested. I tried to use it in my Qlikview and it doesn't seem to be working for me. Wondering if i am doing something wrong.

Re: Dynamic Totaling

lorenzoconforti — Fri, 20 Oct 2017 14:07:54 GMT

Can you please post how is the variable defined? A screenshot of the variable overview table

Re: Dynamic Totaling

mparker123 — Fri, 20 Oct 2017 17:28:21 GMT

Ok I attached the variable overview

Re: Dynamic Totaling

lorenzoconforti — Fri, 20 Oct 2017 19:02:00 GMT

There are some extra bits at the end of the variable definition

The variable definition is different compared to what we were using above; regardless, remove the end part /1000000000 & ' ' & 'B'