The below pulls through monthly costs from an input.
'Cloud Cost Input From Marketing'[by constant: 'Cloud Cost From Marketing Team'."Staging"]
I would like to pull through only the latest available month but have tried lastnonblank and either im doing it wrong or its not working.
Any help would be appreciated,
Best answer by BrunoRodriguez
View original
+5
Hello
If I understand it correctly, you are pulling data from Metric 1 for an specific item of the ‘Cloud Cost From Marketing Team’ list. Since that data is the monthly extract, you are only interested in the latest month.
The question you need to ask yourself is what is the meaning of the latest month. When using LASTNONBLANK, Pigment is returning the data of the latest month that was not blank. However, that could be our current month (Mar 24), but it could also be the data from Dec 23 for a specific item if they have not had any recent costs.
Would that be what you want? If not, could you please let us know exactly what Month are you after?
Cheers!
Hey Bruno,
I am looking to pull through the latest month of data rather than the current month.
In the data currently, that would be Jan 2024.
When i have tried to use lastnonblank it seems to have no effect on the result. Currently, just pulling through all months in the source data.
Thanks!
Matthew
+5
Hello
Perfect, thank you very much for the clarification. As such, I understand you have the following:
Metric 1 = 'Cloud Cost Input From Marketing'
Metric 2 = 'Cloud Cost Input From Marketing'[by constant: 'Cloud Cost From Marketing Team'."Staging"]
Metric 3 = Metric 2[BY LASTNONBLANK: Month]
I suspect that is what you have tried. Have you removed Month as a dimension from Metric 3 since it is no longer needed?
Many thanks,
Bruno
Removing month doesn’t seem to work as currently the above formula pulls through all available months of data (Dec 23 and Jan 24). As such, removing month gives their combined total rather than just the latest
+5
Hello
Does that mean that Month is a dimension in Metric 3? I did not mean removing it as with REMOVE, but from the dimensionality of the Metric. When using [BY LASTNONBLANK : Month] you will be taking data from the latest Month, so you do not need to have Month as a dimension in the target metric (Metric 3)
Cheers!
If i remove it from the dimensionality it flags up an error. It seems as though lastnonblank is not doing as it is intended
+5
Hello,
Are the dimensions in Metric 2 and Metric 3 the same with the only change being that Metric 3 is not dimensioned by Month?
And also, what is the flag telling you?
Cheers!
Yes they’re the same.
Just that Dimension ‘Month’ was removed
+5
Hello
You are completely correct. It was me being wrong. Can you please try [REMOVE LASTNONBLANK: Month].
Please let me know whether it works!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
