6 Replies Latest reply: Sep 7, 2011 9:10 AM by Stefan Wühl

# Daily based calculation in a monthly based dimension

Hi everybody,

I placed a table (diagram) in my application with

- dimension:

month

- formula:

sum all "impressions" from the item, that has the most "impresssions"

Now take a look at my formula, please:

This formula checks, which item has the most impressions. After that the sum()-forumlar is used for the item with the most impressions and will display the value in the diagram. For example:

if  sum("sb")  has more IMPRESSIONS  than  sum("sky")    then    sum("sb")   else     sum("sky").

At the moment the formula cover the whole month (because the dimension is "month"), BUT I need to apply this formula on a DAILY base (to become more exaclty). BUT I HAVE TO DISPLAY THE RESULT IN A MONTHLY DIMENSION AS SHOWN IN THE TABLE's SCREENSHOT ABOVE.

Is there any way to do this?

jup

• ###### Daily based calculation in a monthly based dimension

I need a daily calculation and want to display in monthly based table:

• ###### Daily based calculation in a monthly based dimension

Jup,

I think you could use advanced aggregation for this, just use as expression:

= sum( aggr( YOURCURRENTEXPRESSION, Day))

Regards,

Stefan

• ###### Re: Daily based calculation in a monthly based dimension

Hmmm. Where and how to use it? In the formula-Field of the table?

Let me try to explain again what I need...

Here' s the workflow where the calculation is at the moment:

But this is very imprecise because we compare the SUM() of the whole month for "sb" and "sky".

We need to check wether "sb" or "sky" has the most Impressions für ONE day and use this value to count the impressions for the month:

Any idea?

Please take a look at my attached QVW. You can use it and modify it

jup

• ###### Re: Daily based calculation in a monthly based dimension

Maybe like this ( have a look at the expression in the chart).

Regards,

Stefan

• ###### Re: Daily based calculation in a monthly based dimension

Hi

Here's an alternative where the calculations is done in the load script instead!

(NB! You may need to take Year into account if you will have more than one year in your final app)

hth/gg

• ###### Re: Daily based calculation in a monthly based dimension

Doing the aggregation in the script has the advantage of being more performant.

But it also has the disadvantage that it is not sensitive to selections (on the granular level e.g. selecting a subset of dates).

So, it really depends what your requirements are, if you want more flexibilty, I think you should stick with the aggr() function, but if you experience low performance and you don't need this flexibility / sensitivity to selections, you could go with putting it in the script.