Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
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.
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