topic Re: Issue with expression in QlikView

Issue with expression

Thu, 17 Dec 2015 17:07:38 GMT

Hi,

Below is my expression:

=$(vLogFn($(=

sum({<[Report Date]={'>=$(vToDateMonthStart) <=$(vToDateMTD)'}>} [New Issuance Principal])

sum( {<[Report Date]={"$(=Date(Monthstart(Max({<Metric=,DateFlag={'2'}>}[Report Date]))))"},Metric={"Orig Loans/Advances ($M)"}>} [Forecast Value])*1000000*

num#(day(Max({<Metric=,DateFlag={'2'}>}[Report Date])))/num#(day(monthend(Max({<Metric=,DateFlag={'2'}>}[Report Date]))))

)))

This expression returns -2.28M

But I want to show it as (2.28M) when the result is negative.

So basically the requirement is to show the difference in brackets when its negative and to show it as is when its positive.

Can anyone please let me know what change I need to make in my expression to achieve the result.

I want to display the output in a text box.

Note: LogFn is defined as below

pick(floor(log10(fabs($1))/3)+1, num($1, '#,##0.'), num($1/1000, '###K'),num($1/1000000, '###M'),num($1/1000000000, '###B'))

Thank you in advance.

Regards,

Sachin

Re: Issue with expression

sunny_talwar — Thu, 17 Dec 2015 17:12:08 GMT

May be this:

Pick(Floor(log10(fabs($1))/3)+1, num($1, '#,##0.; (#,##0.)'), num($1/1000, '###K;(###K)'),num($1/1000000, '###M;(###M)'),num($1/1000000000, '###B;(###B)'))

Re: Issue with expression

Thu, 17 Dec 2015 17:17:29 GMT

Super Kool, Sir. It just worked.
Appreciate all your help.

Re: Issue with expression

sunny_talwar — Thu, 17 Dec 2015 17:18:56 GMT

Glad it worked. and you can just call me Sunny my friend.

Re: Issue with expression

Thu, 17 Dec 2015 17:22:25 GMT

Sunny,

But now my other expression

=$(vLogFn($(=sum( {<[Report Date]={"$(=Date(Monthstart(Max({<Metric=,DateFlag={'2'}>}[Report Date]))))"},Metric={"Orig Loans/Advances ($M)"}>} [Forecast Value])*1000000*

num#(day(Max({<Metric=,DateFlag={'2'}>}[Report Date])))/num#(day(monthend(Max({<Metric=,DateFlag={'2'}>}[Report Date]))))

)))

is returning 3.28)

Earlier it was returning 3.28M

There's a brace coming up after the number instead of the letter

Re: Issue with expression

sunny_talwar — Thu, 17 Dec 2015 17:25:04 GMT

What about K? is that working find?

Re: Issue with expression

Thu, 17 Dec 2015 17:27:02 GMT

Re: Issue with expression

sunny_talwar — Thu, 17 Dec 2015 17:28:35 GMT

Not sure why it would work with one expression and not with another. Would you be able to show a sample?

Re: Issue with expression

Thu, 17 Dec 2015 17:31:27 GMT

The dashboard is like 3 GB. Not sure how to share with you.

Re: Issue with expression

sunny_talwar — Thu, 17 Dec 2015 17:32:32 GMT

Screenshots may be with the expressions, output, variable (vLogFn)

Re: Issue with expression

Thu, 17 Dec 2015 17:42:53 GMT

Here it is:

Re: Issue with expression

sunny_talwar — Thu, 17 Dec 2015 17:50:45 GMT

Can you try breaking the second expression with the vLogFn() variable to break in parts and see which part isn't working. This is going to be iterative, but don't know another way of helping you here.