Question

Using LASTNONBLANK with a FILTER OR REMOVE

  • 26 June 2023
  • 2 replies
  • 180 views

Userlevel 2
Badge +1
  • Seasoned Pigmenteer
  • 22 replies

Dears,

I want  to creat a metric or a property dimension where I get the last non blank dimension from a transaction list that also has some constraints like certain dimensions from that transaction list be equal to something. 

I have the following formula:

'01_MRR-T Requests MRR BE'.'Billing Unit'[BY LASTNONBLANK: '01_MRR-T Requests MRR BE'.'Company or Partner Id'][REMOVE:'01_MRR-T Requests MRR BE'.'Product Final'='MRR-D Product final'."Licences"]

Is says that I need to add as a dimension  MRR-D Product final, so even if I just want to use it as a constraint do I need to add to the dimensions in the metric structure? 

 

Thank you very much in advance,

Jose

 


2 replies

Userlevel 4
Badge +4

Hey, 

 

Here you need to apply your filter before the aggregation by Lastnonblank. 

 

Basically once you aggregate by lastnonblank ‘Company or partner’, your result is structure by ‘Company or Partner’. Therefore you cannot go back to transaction list properties which are not structured by any dimenion. 

 

So in you case I would do this : 

'01_MRR-T Requests MRR BE'.'Billing Unit'[FILTER:'01_MRR-T Requests MRR BE'.'Product Final'='MRR-D Product final'."Licences"][BY LASTNONBLANK: '01_MRR-T Requests MRR BE'.'Company or Partner Id']

And I’d say this should do it !

 

Let me know if not !

 

Franck

Userlevel 5
Badge +8

Hey Jose,

Did you manage to get forward with this one? :)

Reply