topic Re: Averages in QlikView

Averages

Mon, 07 Jan 2013 16:49:25 GMT

I am trying to write an expression that extrapolates a period of data over a full year e.g. (sales /current number of months)x 12. I am struggling to do this so when I select a month the expression automatically changes e.g. August 2012 is selected the calculation would be (August 2012 YTD sales/8)x12.

Re: Averages

Mon, 07 Jan 2013 21:25:12 GMT

Hi Tom

In what format is your month data stored? If it is a date field, you could use the Month() function to get the number of the month to use in the denominator.

Otherwise, you will need to turn the text field containing the month back into a date using the date#() function, then use the month() around it.

eg =month(date#('August 2012','MMMM YYYY'))*1 returns '8'

Do let me know if this helps,

Erica

Re: Averages

Tue, 08 Jan 2013 06:20:05 GMT

If u have a date field then use below logic

Sales=Sum(Sales)

current number of months=month(max(Datefield))-month(min(Datefield))

~Kabilan K

Re: Averages

Tue, 08 Jan 2013 06:35:32 GMT

Hi,

=month(max(Datefield))-month(min(Datefield)) // it won't return correct value for some scenario(Month('01/08/2013')-month('03/21/2012')

So, I have changed the expression like below

current number of months=ceil((MonthName(max(Datefield))-MonthName(min(Datefield)))/30)

~Kabilan K

Re: Averages

Tue, 08 Jan 2013 11:28:51 GMT

Hi Erica,

Thanks for your help with this, I used - month(date#(Seldate,'MMMM YYYY'))*1which worked until there was no data for the selected month, when this happened no figures were returned. Do you know a way to rectify this issue?

Many thanks,

Tom

Re: Averages

Tue, 08 Jan 2013 19:19:00 GMT

Not a problem. What expression are you using for the YTD sales? Is this something already stored in a column or are you calculating this via another expression?

Erica

Re: Averages

Wed, 09 Jan 2013 09:35:33 GMT

Hi Erica,

I am calculating this in the same expression, however I was using sales as an example the actual calculation is as below.

=sum({<SelDate={">=$(=date(yearstart(max(SelDate)),'MMMM YYYY')) <=$(=date(max(SelDate),'MMMM YYYY'))"}, [Fee Group]={ Various fee types }>}[Base Currency Amount (USD)])

Tom

Re: Averages

Thu, 10 Jan 2013 20:43:10 GMT

And have you tested the bit separately? Does the YTD bit work on it's own?

Just trying to figure out which bit is the issue

Erica

Re: Averages

Mon, 14 Jan 2013 09:35:03 GMT

I have tested the YTD separatly and it does work on its own.

Tom

Re: Averages

Mon, 14 Jan 2013 20:40:01 GMT

In which field is the missing data that causes the problem? Is it Seldate itself or in a separate field?

Erica