topic Re: Daily based calculation in a monthly based dimension in QlikView

Daily based calculation in a monthly based dimension

Thu, 01 Sep 2011 12:55:36 GMT

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?

thanks for your input

jup

Daily based calculation in a monthly based dimension

Mon, 05 Sep 2011 15:27:32 GMT

Addendum:

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

Daily based calculation in a monthly based dimension

swuehl — Mon, 05 Sep 2011 17:41:14 GMT

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

Wed, 07 Sep 2011 11:56:15 GMT

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

swuehl — Wed, 07 Sep 2011 12:08:31 GMT

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

Regards,

Stefan

Re: Daily based calculation in a monthly based dimension

gandalfgray — Wed, 07 Sep 2011 12:28:18 GMT

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

swuehl — Wed, 07 Sep 2011 13:10:40 GMT

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.