topic Formula in QlikView

Formula

Tue, 01 Jun 2010 14:05:18 GMT

I would like to make a chart, where my numbers must be in %, and I always want to calculate with the same denominator.

At this moment I get for every month 100%, but I would like to get for example ' sum (revenue) / sum (revenue) jan 2008.

Can someone help me with the formula so my division always calculates with the denominator of the same month?

Thanks in advance

Formula

Tue, 01 Jun 2010 15:19:49 GMT

How is the denominator month determined? You will probably need the TOTAL modifier and some Set Analysis.

Something like this:

sum(revenue)/sum({<MonthYr = {'200801'}>}TOTAL revenue)

The TOTAL modifier ignores the dimension. You are getting 100% for everything because both the numerator and denominator are only calculating for the month in the dimension. The Set Analysis then limits the denominator to a specific month. That portion will change depending on your date format.

Formula

Tue, 01 Jun 2010 15:50:10 GMT

Thank you.

It seems that the formula is ok because qlikview doesn't give an error , but I don't receive any results in my graph.

Probably because of the determination of my 'MonthYr', but I have to get a deeper look at it.

Thanks

Formula

Tue, 01 Jun 2010 15:52:42 GMT

If you post a QVW file, it will be easier for us to give you the correct formula (even if you don't give the full data).

Formula

Tue, 01 Jun 2010 16:07:34 GMT

An example would be very helpful.

The Set Anaysis portion is:

...MonthYr = {'200801'}>}...

You need to change MonthYr to the name of your field. Do you have a month/year combined field or individual fields for each? Then the 200801 portion needs to be the format of your field. If you have separate fields, it may be something like:

sum(revenue)/sum({<Month = {1}, Year={2008}>}TOTAL revenue)

These are all just guesses as we don't know what your data looks like.

Formula

Tue, 01 Jun 2010 16:07:44 GMT

Hello,

This is my formula at the moment.

Sum

I am looking if I can post the qvw file without giving the full data, but this I have to find out.

(OmzetGewicht) / Sum ({<MaandJaar = {"200801"}>} TOTAL OmzetGewicht)

Formula

Tue, 01 Jun 2010 16:14:28 GMT

Create a List Box for your MaandJaar field, do the values look like 200801?

Try adding Sum(TOTAL OmzetGewicht) as an expression are you getting values?

I'm guessing the Set Analysis is your problem.

Formula

Tue, 01 Jun 2010 16:28:41 GMT

It was indeed the set analysis. My MonthYear was set up like "jan-2008". Now it works.

Thank you very much!!