Solved

Using something similar to the formula Today() in Excel

  • 23 August 2022
  • 8 replies
  • 627 views
J
Rank
Userlevel 2
Badge +1
  • Jose Seasoned Pigmenteer
  • Seasoned Pigmenteer
  • 22 replies

Dears,

 

I am trying to filter by the current month in each of the metrics, I would use TODAY() function in excel, but since there is no function like that on Pigment. 

As a workaround I created a metric that I called Current Date (Month Dimension) , which I will update monthly, so that I can filter by that metric and automatically update all the other metrics.

 

IF('MRR Be Churn Analysis'=1,TRUE)[FILTER:'Data Hub'::Month='Current Date']

 

However, I did not work, do you have any suggestion or guidance?

 

Thank you very much in advance!

 

Jose

icon

Best answer by Benoit 23 August 2022, 17:34

View original

8 replies

Benoit
Rank
Userlevel 6
Badge +12

Hi Jose, 

This is a good workaround that should work given you have the month dimension in your metric’s structure.

[filter:Month='Current month'] or even [Month='Current month'] should filter the data on your current month.

When you say ‘it did not work” what error message do you have?

 

Furthermore, please note we are working on a solution for this TODAY() function 😊

Thanks,

Benoit

Benoit
J
Rank
Userlevel 2
Badge +1

Hello Benoit,

 

Thank you very much for your reply!

It seems to not be filtering anything, do you have any idea of what can I do?

 

 

BR,

Jose

 

Benoit
Rank
Userlevel 6
Badge +12

Hi Jose,

The formula looks good to me. The filter will not automatically hide the column where there’s no data, it will just display data in the filter column, so in this case in the Month corresponding to your ‘Current Date’ metric.

To try you can use the view dynamic filtering panel and hide empty rows and columns.

If you don’t have any results for the given month, please ensure your data ‘MRR be churn analysis’ should return results. Otherwise, I suggest you to create a Support ticket to we can have a look within your model.

 

Please let me know.

 

Best,

Benoit

Benoit
O
Rank
Badge +1

Workaround for those interested: create a report in salesforce where the only column is a formula for TODAY(). Connect this to Pigment and set the schedule to import daily.

It would be very helpful to have this simple feature available in Pigment natively!

Chris
Rank
Userlevel 7
Badge +13

@ostaiger    We recently added the add load date functionality.  If you already have an import scheduled, you could easily turn this on. 

Chris
Dmitrii
Rank
Userlevel 2
Badge +5

Furthermore, please note we are working on a solution for this TODAY() function 😊

@Benoit are there any updates on the TODAY() function?

Benoit
Rank
Userlevel 6
Badge +12

Hi Dmitrii,

Unfortunately not, no update on this function so far, but there are workarounds, just like Ostaiger described above.

Many thanks for your patience and understanding.

Benoit
E
Rank

Hi Dmitrii,

 

There is a simple workaround in Pigment available with the metric to metric function.
You can have two metrics. 
Metric 1: Formatted as date.
Metric 2: Formatted as date, but with formula metric +1

Then have a metric to metric copy of Metric 2 copy to Metric 1 and schedule that daily at the moment that you want to update the current day. It's not the same as today(), but at least it is all solved within Pigment.

Reply


Ask your question here!

Terms & ConditionsCookie settings