Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

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

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

4 Replies
Taoufiq_ZARRA
Valued Contributor

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

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,


Taoufiq ZARRA
Partner
Partner

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

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

Partner
Partner

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

Hi here is qvw file

Digital Support
Digital Support

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

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.