How to get the first non-blank value from a line item to another module

KacperSzafranski13

How to get a value to another module for the given selectors, the first one which is not blank? I always have one value in historical sales name and I want to transfer it to another module. And then how to transfer the value that is next to the line item Value Min

derektslim

Hi KacperSzafranski13,

I see what you meant, I have tried replicating it using similar build based on your description. Ignore that I had used Week instead of Month like yours, the outcome should be pretty similar.

Understanding the problem

I believe the reason why the Time Summary is showing blank is because we used Formula as summary method and the formula < ITEM(L2 List) > works out to be a blank when it is at the Total (at L1 level, i.e., L2 Parent).

Noticed that the Summary at the System module is blank

If your line item < ITEM(L2 List) > has a summary method (for example below, First non-blank), it will show up, however this is unlikely what you are trying to achieve.

This results in showing up as New South Wales for all the Time Summary (Months | FY | All Periods)

Building the solution

I assume that what you are trying to achieve is finding out which Province did the best for the Month and FY based on ranking of actual sales.

This may not be the most elegant or best approach, so there might be someone who could give a better approach. My approach to this will be to create a Ranking using the Year instead of Weeks (Month in your case) to rank the FY.

This should show up quite nicely, and if you want to take it a step further to just have a single line item, you may consider combining them together using Week (Month in your case) and use a formula to show them altogether.

The formula combines both the lower and higher level time hierarchy together, do note you may have to create another one more without time Dimension for All Periods and add a TIMESUM function to the formula < TIMESUM(Actual sales) >.

Final name formula:
IF
'Historical sales rank 1 name' <> BLANK
THEN
'Historical sales rank 1 name'
ELSE
'Historical sales rank 1 name on year'

Final sales formula:
IF
'Historical rank 1 sales value' <> 0
THEN
'Historical rank 1 sales value'
ELSE
'Historical sales rank 1 sales value on year'

Hope this helps in understanding and pointing you in the right direction!

derektslim

Hi KacperSzafranski13,

I hope I'm understanding your question correctly. There are two ways you may achieve this:

1. Under-dimensionalise your DAT04 module » It seems that your DAT04 ranking module and the data source module have the same dimensions. When you refer over, you will get all the same numbers in the same place. By removing Diet Type, for example, as a dimension, you will then refer to the summary method in Diet Type when you point over the data. Then in the data source module, you will have to include a summary method (as First non-blank or min, etc.,)
2. Add a summary method to DAT04 module » With your DAT04 module referencing to the data source module, you just need to add a summary method so it will show up when you choose Total Diet Type.

My preference will be to go with Method 1. It reduces the size and can act as an intermediary calculation step if you want to show the First non-blank throughout in another line item without using SELECT.

The case for Method 2 is when your underlying data source Summary method has to be different (e.g., uses Last non-blank or Sum instead) from your DAT04 data.

Hope this helps!

KacperSzafranski13

I will try to describe my problem better. I have a module that is built as follows:

L2 Voivodeship has one parent → Country
Z03 - Diet Kcal has two higher levels → Diet → Diet Type

I have four line items:

Where:
Actual Sales (Pckgs) - number of sold diet packages
Historical Sales (Pcgs) Rank Revers - It shows the position for the given voivodeships in terms of the sold quantity of diet packages.

Historical Sales (Pckgs) Rank 1 Value Reverse - It retrieves the number of sold packages from actual sales when the ranking is 1
And I have a problem with Historical Sales (Pckgs) Rank 1 Name Reverse:

Because it shows me the correct values for cumulative diets, but when I want to display the data for FY, I have an empty field.

Dikshant

It would be simpler to enable the Summary to Last Non Blank and use the SELECT function to refer the top level in DAT04 module.

KacperSzafranski13

I'm sorry, but that doesn't tell me much; can you clarify how to create this select? Should I use Select to get voivodeship name or what?