Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I'm trying to write month over month and year over year variance formulas using a pre-built calendar table but realized my data (Alerts) are based on 'LastBusinessDayDate' and not a traditional calendar alert. Because of this, my variances and KPIs are all off by a few numbers. 'LastBusinessDayDate' only pulls alerts that were triggered in my database on a business day, not on any given calendar day.
The formulas I built based on traditional calendar logic are:
Month over Month:
=(count({<LM={"1"}>} distinct AlertNum)-count({<MonthsBacks={"2"}>} distinct AlertNum))
/count({<LM={"1"}>} distinct AlertNum)
Year over Year:
=(count({<MonthNum={"<$(=num(month(today())))"},CY={1}>} distinct AlertNum)-count({<MonthNum={"<$(=num(month(today())))"},LY={1}>} distinct AlertNum))
/count({<MonthNum={"<$(=num(month(today())))"},CY={1}>} distinct AlertNum)
I can't figure out how to get the variances to run using the 'LastBusinessDay' logic instead. Any suggestions? Thank you in advance!
It turns out I could use another field in my script to link to my calendar (Num(Floor(CDSDateTime)) as %CDATE) which saved me from having to create another master calendar. Thank you for the suggestion though!
you can create master calendar using different date fields
https://community.qlik.com/t5/New-to-Qlik-Sense/Two-date-fields-one-master-calendar/td-p/1407528
It turns out I could use another field in my script to link to my calendar (Num(Floor(CDSDateTime)) as %CDATE) which saved me from having to create another master calendar. Thank you for the suggestion though!