Trying to filter by a Yearvalue with a nested Lookup to pull list parent

sbumgarner

I have a module that we export data from using two system type modules that allow a user to filter their export by version and by year (time) using boolean selection boxes (checkboxes).  I want to add the ability to filter by a third category, Division.  The way the module is currently filtering the data in the export action is by relying on a boolean line item that currently has the formula: 

 

Amount <> 0 AND YEARVALUE(Time Selector.'Select Period(s) to export:') AND YEARVALUE(Version Selector.'Select version(s) to export:')

 

In the dashboard that the user opens up the Time Selector and Version Selector modules allow them to check the selections they want to export data for.  

 

The source module with the line item formula above is using the 'location' list as a dimension.  The parent of the location is the division - so I can't directly reference the division in the formula syntax I attempted to add because it gives me the error "level mismatch on common dimension". 

 

My second approach was to nest a Lookup function within Yearvalue however the error it then gives me is that Yearvalue function may not take an expression as its argument, only a line item or property.  For this last part I was attempting to tack on "AND YEARVALUE(Division.Selector.'Select Divisions to export:'[LOOKUP: Division]).

 

Is there another way I can incorporate the Division filter into my line item boolean formula so that the user can only get the divisions they want rather than all divisions as it currently stands?  I have included a screenshot that might help visualize this.

ShubhamCh

Hi @sbumgarner 

 

I am not sure why you are trying to add Yearvalue() in the existing filter's formula. Here is my solution:-

• Create a line item in your source module call it "Division" division formatted. Formula:- Parent(Item(Location)), you can also reference the item() from sys module if you have one.
• Create another line item, "Selected Location" boolean formatted. Formula:- "Your Selectio Boolean where user will select the division"[Lookup:Division]. This will bring those user selected booleans corresponding to the children.
• Add this "Selected Location" in your filters' formula as an "AND" condition. It'll work fine. You can turn the summary on as per your requirement for the new boolean.

Attaching some mock up screenshots :- 

 

Thanks!

-Shubham

sbumgarner

I see what you mean - I was trying to use Yearvalue on Division when it wasn't necessary to do it in that way in order to add it as a filter.  It works now - thanks.