Can ANY be used where the source contains TIME dimension?

Options

Hi All,

I am try to query the presence of a list item in a source mapping where the source contains TIME dimension.

Source contains one list combined with TIME where a mapping can differ across a year.

I want to query this mapping to determine if a target list is in use at any point during that time period.

I use this often with non time dimensions and it works well but with TIME as a second dimension in my source the ANY is not returning a TRUE outcome even when the source contains a sample list item.

Why?

Answers

  • This is my source module containing the boolean to aggregate and my mapping line items ;

    Here is the target;

    The dimensions of my mapping match my source (its the same module ) while the dimensions of my target match the formatting of my mapping line items…Im baffled why this isnt working!

  • @ChrisAHeathcote

    Do you have All Time Periods checked for the timescale? If not, you will need it for the data to aggregate up the months

  • Yes, I have 'All Periods' and Im using ANY summary settings.

  • But as the mapping can differ by TIME I need the aggregation to work across individual time periods.

  • Is it not working because formulas will not automatically default to selecting the 'top level' for time as it doesnt technically have one…we need to use SELECT function?

  • I solved it - added month to my target, "Employee?" line item and used TIMESUM to reference the All Periods aggregation to remove TIME for the final target line item "Build?"

    This does what I need it to do!

    But would love to know why the ANY will not work when the source has TIME. I use this with other modelling where TIME is not present in the source and it works perfectly.

    What is it about the TIME dimension that doesnt allow us to include it here?

  • @ChrisAHeathcote

    Yes, in my test, I had to use the Select for All Time periods.