Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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.
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/DateAndTim... )
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
Try this
Sum({<OrderDate = {">=$(=MonthStart(AddMonths(Max(OrderDate), -1)))<=$(=MonthEnd(AddMonths(Max(OrderDate), -1)))"}>} Total Revenue)
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)
Hi
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