topic Re: Measures that calculate KPIs for the previous month. in App Development

Measures that calculate KPIs for the previous month.

krisk — Thu, 27 Apr 2023 12:57:08 GMT

I am entirely new to Qlik and I have only just started to try and build some measures. I am entirely new to the syntax, but I have read about the basics of measure syntax.

I am using a dummy data set so the data is not real, and it is not up to date or recent, I think the latest date in the data is around 2017 and the measure I am trying to create is based off of months and previous months.

I created a test measure called Total Revenue and used the syntax:

Total Revenue = Sum(OrderQuantity)*Sum(ProductPrice)

I'm aware that even this syntax may be wrong (any advice on that would be greatly appreciated too), but I then wanted to create a measure that I could use for a KPI showing the previous month's revenue based on the first measure.

I have read some other forum posts on similar issues but I am having problems changing the measures to suit my needs.

Any help on the matter would be greatly appreciated.

Thank you for reading.

Re: Measures that calculate KPIs for the previous month.

edwin — Thu, 27 Apr 2023 14:18:25 GMT

first off, you dont multiple the sums to get the revenuew, you sum the product:
Total Revenue = Sum(OrderQuantity*ProductPrice)

there is a post somewhere that discusses the AS OF CALENDAR - cant find it rt now but others may have it in their FAV or you can look it up.

there are also other ways of getting data for previous months - one is to build a bridge (in line with the as of calendar) or simpler create variables and use that in your set analysis.

for example if the user selects a MONTH, you can create a variable that calculates the previous month (see https://help.qlik.com/en-US/sense/February2023/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunctions/addmonths.htm )
you can then store that in your variable and in your expression:

Revenue:
sum({<MONTH={'$(vPreviousMonth)'}>}Quantity * Order)

assuming that the user selects the MONTH field and the variable vPreviousMonth gets the date for previous month. o course all this will depend on how you store values in MONTH and your data model

Re: Measures that calculate KPIs for the previous month.

Chanty4u — Thu, 27 Apr 2023 17:38:37 GMT

Try this

Sum({<OrderDate = {">=$(=MonthStart(AddMonths(Max(OrderDate), -1)))<=$(=MonthEnd(AddMonths(Max(OrderDate), -1)))"}>} Total Revenue)

Re: Measures that calculate KPIs for the previous month.

krisk — Fri, 28 Apr 2023 15:20:06 GMT

Thank you for this @Chanty4u!

I have tried to use this in a master measure but get the following error:

Is the entire condition meant to be in green like it is a string? or should it have more string breaks in it? (apologies if that is a stupid question, I am still trying to get my head around syntax etc)

Re: Measures that calculate KPIs for the previous month.

Chanty4u — Fri, 28 Apr 2023 16:44:29 GMT

Then you have debug the expression by parts

=Max(OrderDate)

=AddMonths(Max(OrderDate), -1)

=MonthStart(AddMonths(Max(OrderDate), -1))

=MonthEnd(AddMonths(Max(OrderDate), -1))

And check where is the issue...and check the order date is formatted