en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Elvira Smit  
#1 Posted : Tuesday, November 6, 2012 10:25:46 AM(UTC)
Elvira Smit
Rank: Newbie

Groups: extranet\Forum
Joined: 4/3/2012(UTC)
Posts: 2

Hello all,

Background
We buy used parts from our customers and sell used parts to our customers. This is shown in a measure. The value is either positive (sold used part to customer) or negative (bought used part from customer).

What I need
I need a bar chart showing our total revenue on used parts per month. This means I only need the positive values from the measure to be added up.

Problem
I have used the visibility agent to hide all members <0. This works perfect when I make a bar chart showing the revenue per day. However, when showing the revenue per month, this does not work: it now hides the total value if negative. But this total value often contains a few positive values: these have been added up to the negative values and are not considered separately.

Do you know another way to solve this problem?

Thanks for you help and have a nice day,

E. Smit
David L. Bojsen  
#2 Posted : Tuesday, November 6, 2012 10:32:48 AM(UTC)
David L. Bojsen
Rank: Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 6

esmit@bolier.nl wrote:
Hello all,

Background
We buy used parts from our customers and sell used parts to our customers. This is shown in a measure. The value is either positive (sold used part to customer) or negative (bought used part from customer).

What I need
I need a bar chart showing our total revenue on used parts per month. This means I only need the positive values from the measure to be added up.

Problem
I have used the visibility agent to hide all members <0. This works perfect when I make a bar chart showing the revenue per day. However, when showing the revenue per month, this does not work: it now hides the total value if negative. But this total value often contains a few positive values: these have been added up to the negative values and are not considered separately.

Do you know another way to solve this problem?

Thanks for you help and have a nice day,

E. Smit


Hi Elvira

I think you can solve this by making a calculated measure with the formula "if sum(0,0,m1) > 0 then sum(0,0,m1) else 0", hiding the original measure totally,
and then don't use any visibility agents.

The formula is written by hand and have not been tested, but i think this should work.

/ David - TELMORE
Elvira Smit  
#3 Posted : Tuesday, November 6, 2012 12:08:52 PM(UTC)
Elvira Smit
Rank: Newbie

Groups: extranet\Forum
Joined: 4/3/2012(UTC)
Posts: 2

Hi David,

Thank you for your quick response.

The calculated measure works when I use the dimension months with the possibility to drill down to days. However, when I look at the monthly totals, they still show the totals for both positive and negative values...

I have hidden the original measure as proposed and removed the visibility agents.

Any clue on what I am doing wrong here?
Niels Thomsen  
#4 Posted : Tuesday, November 20, 2012 2:43:15 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 105

Hi Elvira

I think you will have to resummarize your subtotals now that the leafdata has been manipulated.
If you add this calculation...

if count(d1, all(c), m2) > 0 then sum(d1, all(c), m2) else sum(d1, 0, m2)

...I think this will fix it. The if-statement examines your calculated measure (m2) column and by counting children it identifies the subtotal level (in your case months). When a subtotal level is identified we sum up all the children (days) of the column - which as a result of your previous calculation only includes the positive values. The else makes sure that leaf data is also transfered to the resulting column.

Hope it makes sense - otherwise please post a reply.
Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error