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.
Comments
-
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.
0 -
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.
0 -
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
2 -
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
0 -
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.
3 -
Dear Team,
I got stuck in one place while building SYS 14 module, i don't have my old Data Hub model - After 90 days WS models gets expired.
Downloaded Data Hub from training - it was to use a formula that references the entire timescale of the DAT05 Historic Revenue by Product module to filter for revenue greater than 0.
Time scale of DAT05 - QTR?
Dimensions of DAT05 - Products and Accounts Flat.
Do I need to refer DAT05 Module with rev>0? i.e 'DAT05 Historic Revenue'.Revenue > 0
I tried but I am missing something here. Kindly guide what should be the approach to resolve this.
0 -
Hi @SaraBTIT @kunal_311 @Misbah ,
I tried to refer the SYS12 SKU Details and SYS13 Account SKU Filter, but I am not successful. So I used the second method in my Level 2 Data Hub model using a formula TIMESUM('DAT05 Historic Revenue by Product'.Revenue) > 0, then the SYS14 looks like:
Could you confirm that we can have the same results with two approaches?
I am looking forward to hearing from you soon.
Best regards.
1 -
For those that come across a similar issue, I would recommend utilizing the TimeSum function similar to the one built in the Data Hub SYS 13 module line item filter labeled "Has Data?".
For the other approach, note that flat lists are used in the Data Hub model and to use the Lookup function a hierarchical list is used. I could not find a good explanation/ help when trying to use a lookup function on flat lists.
0
