Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average of current month variable and previous month

I'm new to QlikView and am struggling to write a formula for the following scenario:

- The average of a variable (vLodgements) for the current month and the previous month, when only the current month is selected.

Does anyone know what I should be using? This is what I have at the moment & it's not working:

 

if(count(DISTINCT [Fin Period]) = 1, (sum({$ <[Fin Period] = {"<=$(vMaxFinPeriodComp)"} - {"<$(vMinFinPeriodComp)"},[Fin Year]=,[Fin Month]=, [Fin Week]=>}$(vLodgements)

3 Replies
lironbaram
Partner - Master III
Partner - Master III

hi i would suggest to include in your calendar table

a MonthYear field who will hold the first date of the month as value , you can create it using the monthstart function

then your function shuld look like

f(count(DISTINCT [Fin Period]) = 1, (avg({$ <[Fin MonthYear] = {">=$(=addmonths(max([Fin MonthYear] ),-1))"} ,[Fin Year]=,[Fin Month]=, [Fin Week]=>}$(vLodgements)

Not applicable
Author

Thank you.

Do I need to add this into the calendar script? Can you give me an example of how I would do this? I'm not familiar with the Month Start function.

Thanks again.

lironbaram
Partner - Master III
Partner - Master III

let say your calendar script looks like:

load date,

          year(Date) as Fin Year,

  .....

then you just add new line

         monthstart(Date) As Fin MonthYear

very simple