Can anyone tell me what these functions are for?
I have read Anapedia but still having a hard time understanding what they do.
MONTHVALUE
HALFYEARVALUE
QUARTERVALUE
WEEKVALUE
YEARVALUE
In the sample below, I guess that "each weekly sales" are compared to "average weekly sales."
And it turns out to be "True" if a weekly sales is greater than average, but I don't understand why MONTHVALUE has to be used, instead of WEEKVALUE.
Examples
In this example, a Product Sales module has line items on rows and Time on columns. The Net Profit line item has a numeric data type, and the Week above average for month line item has a Boolean data type.
In Blueprint, in the Summary column for Net Profit, Time Summary is set to Average. The formula returns a TRUE result for values that are above average for each week in the month.
Week 1 FY21 | Week 2 FY21 | Week 3 FY21 | Week 4 FY21 | Jan 21 | |
Net Profit | 833 | 860 | 867 | 812 | 843 |
Week above average for month Net Profit > MONTHVALUE(Net Profit) | ✔ | ✔ |
Solved! Go to Solution.
@KOBAYASHI One example for you.
Summary method is Closing Balance:
Jan 21 | Feb 21 | Mar 21 | Q1(Closing Balance) | ||
A=QUARTERVALUE(B) | 3 | 3 | 3 | ||
B | 1 | 2 | 3 | 3 | |
Quarter Time Period | Q1 | Q1 | Q1 | ||
C=B[LOOKUP:Quarter Time Period] | 3 | 3 | 3 | ||
Hi @KOBAYASHI
Very good question.
Let me use a bit simplier example, maybe this will clarify it to you.
Let's say you have a simple year filter module managed by user. (User selected FY21)
FY21 | FY22 | |
Filter Boolean | TRUE | FALSE |
Also you have a published module with some monly data in it. This is the result of the calculation for the formula:
Jan 21 | Feb 21 | ... | Jan 22 | Feb 22 | |
YEARVALUE(Filter Boolean) | TRUE | TRUE | TRUE | FALSE | FALSE |
So what formula does - it just literally takes year value for the months. You could use [LOOKUP:<YEAR>] instead, but it is not always possible.
These functions will return values from the time level mentionned in thier names no matter what formula is in the source or even without any calculation.
Kirill
@KOBAYASHI Another thing to add is that each of the functions are referencing the upper level and have to be used in the lower levels of time schale:
YEARVALUE - Days, Weeks, Months, Quarters, Half-Years
HALFYEARVALUE - Days, Weeks, Months, Quarters
QUARTERVALUE - Days, Weeks, Months
MONTHVALUE - Days, Weeks
WEEKVALUE - Days
@KirillKuznetsov san
Thank you very much for your detailed explanation.
May I ask you another question to check if my understanding is on the right track?
[Question]
Let me assume that there exist line item "A" and "B" whose data format are both "date period: month"
If "A" takes "B" as an argument in YEARVALUE function, what is set up in Summary column in "B" (Sum, Average, etc.) affects the return value of "A"?
@KOBAYASHI One example for you.
Summary method is Closing Balance:
Jan 21 | Feb 21 | Mar 21 | Q1(Closing Balance) | ||
A=QUARTERVALUE(B) | 3 | 3 | 3 | ||
B | 1 | 2 | 3 | 3 | |
Quarter Time Period | Q1 | Q1 | Q1 | ||
C=B[LOOKUP:Quarter Time Period] | 3 | 3 | 3 | ||