Year over Year report

[Former Member]

Hello,

I need to build following report-

Jan08 feb08 march08..nov08 jan07 feb07 march07.. nov07 Median08 Median07

Now user wants to see the keyfigure Cases Ordered for all this months in keyfigure.

later want to find the Median value of Keyfigures for January to November.

Now the Month range January to November is not fixed, user wants the flexibility to see any number of months in any order for e.g. (jan to march, or feb to sept. or aug to oct etc)

I have Company number as characteristics in Rows.

What i did ?

I have company number in Rows

24 Restricted Keyfigures in Columns in total,

22 kf are for 11 months each for current year & previous year, 2 kf with formulae to find median.

Now i cannot use Calendar Month & Year in columns like below

COLUMNS

calendar month year

keyfigure structure

current year cases

previous year cases

Because i also need to create formulae, so i cannot use above design.

So i have to build 22 restricted keyfigures.

Now question is , if user is running the report from jan to august, the other keyfigures for sept. oct & nov. still shows up.

They dont display vallues but blank columns still shows up.

How to remove those?

I used Supress Zeroes from Rows & Columns, but this does not get rid of empty columns.

Please advise

Edited by: kartikey on Oct 1, 2008 3:16 PM

[Former Member · Accepted solution]

When I did something rather similar to this, I had to use Boolean logic in the Calculated key figure to ensure the data isnt reported for months you don't want to show.

But I'm not sure the answer isnt simpler than that... you say you have used zero suppression, but I guess you mean you have done that within query properties? Try saving the key figure structure as a global structure, when you do that check the box that says 'Also use zero suppression for structural elements'.

Hope that helps... points?

Patrick

[Former Member · Accepted solution]

How do u use the boolean logic?

can you please specify more with giving more explaination.

Points awarded but my question is not answered yet completely.

Any suggestions please advice experts !!!!

[Former Member · Accepted solution]

<Font Face = "Tahoma">

Hi,

Boolean Logic in BEx Queries:

You can carry out comparisons in formulas

The operators =, <>, >, >=, <, <=, AND, OR, XOR and NOT are available

With Boolean Operators you can use constructions that represent u201AIF THEN ELSEu2018 scenarios

E.g. If you pay a certain commission rate based upon thresholds, boolean operators are used to determine if the threshold has been met and then to multiply out the amount. In this example, the standard commission is 1% of the order value, but if more than $3000 is bought then the commission rate is 2%.

Constructions such as (Order Value >= 3000) or (Order Value < 3000) are automatically defined as '1' if 'true' and '0' if 'false'.

To calculate the commission, then, the order value is multiplied by the appropriate percentage based upon the threshold reached (1 or 2 %)

Everything as said can be done using calculated key figure.

Regards,

Abhijit

</Font>

[Former Member · Accepted solution]

<Font Face="Tahoma" Color="Blue">

Hi,

Please let me know if you have any doubts in what I have written above.

Regards,

Abhijit

*PS: Please reciprocate by assigning points if above response was useful

</Font>

[Former Member]

Hi kartikey.

how did you calculate de median?