How to show only the last uploaded data from a module in a dashboard

sreeharikv

Dear Community,

 

Could anyone please  help me to implement a requirement? 

 

We are getting a monthly file from various manf. locations that tells the available inventory bucket values for various product lots. Here is a sample of the file we are getting

I am loading the file every month to a Data module dimensioned by Manf.Location,  Product Lot and time ( month scale).  In a dashboard  I need to show a static report that always show the last uploaded data (ie. oct 2020 till another set is uploaded next month). The report format needed is like below

 

User should be able to Select a Lot# and then it should show the last uploaded data like this

 

 

Thanks for your help...

Hari

 

ChrisAHeathcote

@sreeharikv 

I would start by reviewing how you are importing your data. By using multiple dimensions in the import you are creating a significant amount of sparsity as it is very unlikely your data will be relevant across all intersections of the various dimensions.

Redesign your data import to come into the model as a flat file. To do this use a numbered list and import your data using a combination of text formatted properties so that you can create a unique code. I would suggest from your query that you need to combine the following; Location, Lot#, available quantity and time. If still you have duplicated across these properties you will need to add more properties. 

Once you have your data in a list create a data module dimensioned by your import list and pull the properties into the module using FINDITEM to convert them into list formatted line item. 

Filter this module by a time filter set to show the current period.

There is a lot of moving parts here. Let me know where you need further clarity.

Chris

rob_marshall

@sreeharikv @ChrisAHeathcote 

 

Loading data into a module that is multidimensional is not a bad thing, but @ChrisAHeathcote is correct in that I would look into creating a unique ID.  But I differ from Chris in that you do not and should not have transactional data or time period as part of the code.  Just by looking at the screenshot, I would advocate for the code being Location concatenated by Lot (LocA_Lot1).  That would be your main transactional list.

 

Then, have a module dimensionalized by Time (month level) and this list.  In this module, have your transactional data (Availably Qty, Expired Qty, and Quarantined Qty).  Have a another module for the metadata, again by the transactional list with line items for Location and Lot (bot can be derived from the code), Lot Expiration Date (or is really just metadata from Lot?), and Lot Exp Date (or does this change?).  If Lot Expiration Date is only tied to Lot, have a flat list of Lots and store this data in that module.

 

Please stay away from properties on the list.

 

Rob

sreeharikv

Thanks @ChrisAHeathcote and @rob_marshall. It is really helpful. 

 

This is going to be a user upload from dashboard. And since this is a user upload, I am using an intermediate module based on a numbered list to concatenate the properties for Finding master lists items. I have a master list for locations and Lot and exp.date is a property of lot. I am not trusting the exp. date in the file upload. I understood this multiple dimensions are bringing significant amount of sparsity. I will try to revisit my approach as rob mentioned.

 

The end goal I am trying to achieve is to have a view that shows the final set of data corresponding to a location. If on October and I do not have the data from a location, I should show the data from September. User should be able to choose a location in some way too.  

 

 

Thanks 

sreeharikv

.@rob_marshall ,

 

I created two modules as you suggested, One with numbered list with month dimension and one with numbered list only. In the first module, I am keeping a list lineitem 'Current' to mark as the current month data. This line item will have data for only one month. For all other months it is blank. I am using this line item to mark the month as last month with transaction data. The summary is set "Last non blank".  

 

Is there a way to copy  line items from one particular month marked as "Current" in the time dimensioned module to the summary module? When I used lookup it throw an error "Format of mapping used for lookup doesn't match any dimension of the source". 

 

Thanks

ChrisAHeathcote

@rob_marshall 

I wouldnt normally advocate loading time into a list property but to create the report @sreeharikv is looking for then I think it would be the simplest solution.

However, I acknowledge that it is not best practice to load data into list properties and the combination of properties feature for a numbered list should only be used to create the list code. 

If the user is comfortable viewing the report as a series of nested rows then @rob_marshall  is correct in his solution. However, be mindful that you are unable to nest more than three dimensions in either rows or columns so if you need to view more then a combination of approaches may be more suitable. 

Good luck.

 

rob_marshall

@sreeharikv ,

 

Quick question, do you or will you need the previous months data?  I ask because you stated you only needed the "latest" month, so will there ever be a report which states the amount from a previous month?  If not, then you will not need the module dimensionalized by Time, just have that as a line item.

 

Rob

otalpur

A couple of options

 

1. If your loading against time, wouldn't the last nonblank time be the last upload date?

 

2. As part of your import process, you could just create another step or two that loads the time period (Oct-20) into a line item that is dimensioned by the Lot. Since it looks like your file has both Sept and Oct, it would take the first occurrence so would have to have a 

sreeharikv

Thank You all..

 

I need the previous months data for historical storage purposes. But only the last month uploaded data will be referenced while doing the inventory calculations.

 

I was able to get what I need using the select "All time". I enabled 'All Time" in the model calendar. I created a lineItem "LastReportDate" to hold the month name of the last upload. Set the summary for that line item as "LastnonBlank". From the summary module I got the last uploaded month  using the formula

<sourceModule>.LastReportDate[SELECT: TIME.All Periods].

 

Once, I got the month, I used Lookup to get all the necessary line items using

<SourceModule>.OnHandQuantity[LOOKUP:LastReportDate]

 

 

I am not sure if this is a better approach, but this is working as expected.

 

Thanks

Hari