Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Averages

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.

9 Replies
Not applicable

Re: Averages

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

Not applicable

Re: Averages

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

Not applicable

Re: Averages

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

Not applicable

Re: Averages

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

Not applicable

Re: Averages

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

Not applicable

Re: Averages

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

Not applicable

Re: Averages

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

Not applicable

Re: Averages

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

Tom

Not applicable

Re: Averages

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

Erica