List subset by formula

yoskaneko
yoskaneko
in Modeling

Dear gurus,

 

I'd like to create inventory report and included "Item master" as dimension.

However, since there is no stock in 50% of item master, it is obviously waste of cells.

So, I'd like to create list or list subset based on "existing stock only".

 

One of idea is to create simple module with item master, and add property as boolean with

formula that "IF Qty > 0 THEN TRUE ELSE FALSE", then import this boolean to

list subset finally.

But, since qty of inventory is monthly basis, but item master does not have time dimension,

I'm not sure how to create formula such as "IF QTY > 0 in Jan or Feb or Mar... THEN TRUE

ELSE FALSE".

 

Yoshi

Tagged:

Answers

  • Kyle
    Kyle

    Hi Yoshi, 

     

    You can create a line item in the module that does not have time as a dimension and use a formula to reference the total value. If you have the 'All Periods' time then you can write a select statement to reference it, or if you utilize the current period feature, then you could write the select statement using current period. This second option would require other line items to ensure that the current period has the total value. In the screenshot, I used a line to cumulate all the values and then a line to grab the yearvalue. Both of these options allow for a sustainable model where you would not have to update the formula year over year. 

     

    If neither 'All Periods' or 'Current Period' are an option, then you could reference the specific full year value in the select statement using Time.'FY17' but this would have to be adjusted each year.

     

    One thing to note is that I had to pivot so line items were in columns in order to get the import into the list to work. 

     

    I hope this helps!

     

    Kyle

  • harish_bk
    harish_bk

    Hi The other way would be to replace the item master list by counter list in the module like 1,2,3 being list items.

     

    Then you can create one more line item called master list which is "Master List" Formatted and then submit your numbers as required.

     

    Then use the sum function to get the final numbers into another module so that you need not worry about maintaining the subsets.

Categories