I have created a metric. Based on current date it will fetch the current month.
I have another metric for defining Historical period. I am trying to drive this by above grid.
However I am getting an error while applying the formula.
What is that I am missing or doing wrong. Please suggest the right logic
Best answer by Thu Mai
View original
+6
Hi Mukul,
Your formula is trying to compare 2 dimensions data type metric - Month, and as the error indicate, the only syntax allowed for dimensions comparison is “=” or “<>”, not “>” or “<”
The hint here is use the start date/end date of the month and then run the comparison. To get History period boolean in this case, I suggest you use this formula:
IF(Month.'Start Date' < 'Current Day', TRUE, BLANK)
Why BLANK and not FALSE? For IF argument you don’t really need, you should put BLANK instead of FALSE, why? As although the results are still correct but FALSE will densify your metric and effect overall related performance unneccessarily.
Hope my answer helps 😊
Hi Mukul,
Your formula is trying to compare 2 dimensions data type metric - Month, and as the error indicate, the only syntax allowed for dimensions comparison is “=” or “<>”, not “>” or “<”
The hint here is use the start date/end date of the month and then run the comparison. To get History period boolean in this case, I suggest you use this formula:
IF(Month.'Start Date' < 'Current Day', TRUE, BLANK)
Why BLANK and not FALSE? Looking at your formula, the other problem I forsee here is you use FALSE for the second arguments of your IF. As for this boolean, you only care about the Months that are history, then you don’t really care about the rest of other months. For IF argument you don’t really need, you should put BLANK instead of FALSE, why? As although the results are still correct but FALSE will densify your metric and effect overall related performance unncessarily.
Hope my answer helps 😊
Thank you for the response. I agree that I should put blank as compared to false.
Regarding comparison, Does this mean it will always be dates to be used for comparison and never the dimensions? What if I want to compare one month with another as dimension? Is there a workaround?
+12
Hi,
For comparisons you must use properties than can be compared, dates, or numbers.
A dimension item by default is a text property.
Hence why you can’t do April - January, like you can’t compare a - b.
However you can compare dates, April, 1st - January, 1st.
I hope this helps.
Hi,
For comparisons you must use properties than can be compared, dates, or numbers.
A dimension item by default is a text property.
Hence why you can’t do April - January, like you can’t compare a - b.
However you can compare dates, April, 1st - January, 1st.
I hope this helps.
Still not working for me. Even when I am comparing date Vs Date, it is giving an error. What I am doing wrong here?
current day
+6
Hi @Mukul,
Your formula is not correct, for 2 reasons:
1. Month.'Start Date' returns a result dimensioned by Month, meanwhile your 'Current Day'.'Current Date’ returns a result dimensioned by the 'Current Day' transaction list → Dimensions mismatched
2. Comparison syntax should be “<” not “=” to seek History months
To make things simple, I suggest:
1. Current Day input should be a date metric, not a transaction list;
2. Formula should be: IF(Month.'Start Date' < 'Current Day', TRUE, BLANK)
Hi @Mukul,
Your formula is not correct, for 2 reasons:
1. Month.'Start Date' returns a result dimensioned by Month, meanwhile your 'Current Day'.'Current Date’ returns a result dimensioned by the 'Current Day' transaction list → Dimensions mismatched
2. Comparison syntax should be “<” not “=” to seek History months
To make things simple, I suggest:
1. Current Day input should be a date metric, not a transaction list;
2. Formula should be: IF(Month.'Start Date' < 'Current Day', TRUE, BLANK)
Hi
should come from metric and not as a list property,correct?
+6
Hi
No, I don’t mean that as it depends on multiple factors, in short, when you want to compare blocks to each other, just make sure they’re compatible in terms of data type and dimensions.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
