SYS14 Automatic sum over Flat List not possible

Highlighted
Occasional Contributor

SYS14 Automatic sum over Flat List not possible

The line item "Data?" in Data Hub Model: SYS14 module was asked to reference SYS12 SKU Details and SYS13 Account SKU filter which were created during Level 2 Data Hub. However, both SYS12 and SYS13 were using SKU Flat as dimensions (as instructed in Level 2 training). When trying to reference the two modules, I got error indication "Invalid formula for 'SYS14 Create Account>Product'.Data?: Automatic sum of 'SYS13 Account SKU Filter'.Has Data? over SKU Flat hierarchy is not possible as SKU Flat does not have a built-in top level".  

Shall I assign parents and top levels for the SKU flat list? or change SKU12 & SKU13 dimensions from "SKU Flat" to "P3 SKU"? I know the problem is not from the formula but from the list structure.     

 

5 REPLIES 5
Highlighted
Master Anaplanner/Community Boss

Re: SYS14 Automatic sum over Flat List not possible

No, you don't want to do that. Keep the dimensions as Flat list and not hierarchical list.

 

Can you please show us which line item in SYS14 module are you writing the formula - As far as I know SYS14 has both account and product as dimension so there won't be automatic summing required.

Highlighted
Occasional Contributor

Re: SYS14 Automatic sum over Flat List not possible

Thanks for your reply Misbah. I'm writing formula for "Data?" line item in SYS14.  Yes SYS14 uses Accounts Flat and Products Flat as dimensions, and Level 3 instructed that the line item "Data?" shall reference module SYS13 & SYS12 which were created during Level 2 training. The automatic sum error happens because both SYS13 and SYS12 were using SKU Flat as dimensions.  

Highlighted
Master Anaplanner/Community Boss

Re: SYS14 Automatic sum over Flat List not possible

@SaraBTIT 

 

 I think what you want to check in DATA line item of SYS14 module is if a particular combination of SKU & Account has data or not. This information is already available in SYS13 module (atleast for me it is). In this line item you can just refer SYS13.

 

If you don't have it in SYS13 then you can write the formula in the DATA line item of SYS14 module. We are not supposed to give share formulae but i can give you a hint : See other SUM functions in sync with Time

 

Hope this helps

Highlighted
Contributor

Re: SYS14 Automatic sum over Flat List not possible

Hey Misbah,

 

I'm referencing the "Has Data?" line item in the SYS13 Account SKU Filter Module to drive the boolean Data? line item in SYS14 module and all the values are populating blank or "false". Would you mind advising what the potential issue may be?  I'm relatively certain the formula used in the SYS13 module is correct based on your comments above... SUM..TIME

Highlighted
New Contributor

Re: SYS14 Automatic sum over Flat List not possible

Hi Everyone,

 

There are two ways to approach this line item as stated in the learning directions.  If you are going to use SYS12 and SYS13, I will provide a big hint if you are getting the error that started this discussion. 

 

You are essentially trying to aggregate for each account the product level from the SKU.  Simply stated “are there any Account SKU’s that have data under each product?  Since SYS14 and SYS13 both use the Account flat list as a dimension you must ask yourself what I can use to aggregate SKU’s to Product when working with no hierarchies and a Boolean.  This has been answered by AP in the question by telling you to look at SYS12 where when building the datahub you imported the product for each SKU to later build a hierarchy list. 

 

If you just want to sum time then you have actually used the function before in your datahub and simply need to change the module and line item you are aggregating. 

 

Hope this helps and I would suggest trying both methods as they help cement concepts.