topic Re: How to add calculation in expression that based on the value from dimension field. in QlikView

How to add calculation in expression that based on the value from dimension field.

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

Hi All

How can I use the formula you giving in form of table.

Calc1 = Billed Revenue in Close Month [Jun only] / Total Billed Revenue of entire month [Jan-Dec]

I want to make

| Month | Calc1 |

| May | 0.289 |

| June | 0.301 |

im using expression below but the calc1 shows 0 and not showing any dimension in table.

Expression:

sum ({< OppForecast= {'1'},[SPM Close Month] = {'$(=Month([SPM Date]))'}>}[Est. Billed Revenue For Current Year])/ sum ({< OppForecast= {'1'},>}[Est. Billed Revenue For Current Year])

Dimension:

=Date([SPM Date],'MMM-YY')

Re: How to add calculation in expression that based on the value from dimension field.

Taoufiq_Zarra — Fri, 06 Dec 2019 08:09:03 GMT

maye be

Expression: sum ({< OppForecast= {'1'},[SPM Close Month] = {'$(=Month([SPM Date]))'}>} [Est. Billed Revenue For Current Year])/ sum ({< OppForecast= {'1'}>} [Est. Billed Revenue For Current Year])

can you share a sample data ?

Regards,

Re: How to add calculation in expression that based on the value from dimension field.

Anonymous — Fri, 06 Dec 2019 08:39:34 GMT

if your expression is exactly the same you are using, there is a comma to much in your sum expression and before the character >

sum ({< OppForecast= {'1'},>

Re: How to add calculation in expression that based on the value from dimension field.

A_Jafery — Mon, 09 Dec 2019 06:53:33 GMT

Hi here is qvw file

Re: How to add calculation in expression that based on the value from dimension field.

Brett_Bleess — Mon, 16 Dec 2019 21:28:41 GMT

You may want to comment on whether either of two posts helped or not as well... I would suspect folks have been hesitant to spend more time on things not knowing if you checked what they suggested in those posts? Once you comment on that, I would expect you may get some further responses.

Here is a Design Blog post on Set Analysis that may be of some help too:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

There are some further links to other blog posts at the bottom, just FYI. Hopefully these may help you too.

Regards,
Brett