CR Running Total distinct count duplicates

[Paulo R]

Hello all,

Using this sample data, I want to count how many times each item shows.

Group----item----expected result
1---------A---------1
1---------B---------1
1---------C---------1
2---------A---------2
2---------B---------2
3---------A---------3
3---------C---------2
3---------D---------1
3---------B---------3


Created a RT summarizing the <item>, count, evaluating each change of <Group>

Please note: The <item> shown is the result of a formula, not data directly from a field.


I know I'm missing something here, can anyone assist me?

Thank you,
Paulo R

 

[pmax9999]

Please provide the code for the Item fomula, advise the location (section) where you have located the running total, and explain how the actual result differs from the expected result.

 

[Paulo R]

Hi pmax999,

Below the formula:
if right({SPRICLIST.PLI_0},2) = "50" then
{ITMMASTER_MDL.ITMREF_0}
else
if right({SPRICLIST.PLI_0},2) = "90" then
{ITMMASTER_TSI.ITMREF_0}
else
if right({SPRICLIST.PLI_0},2) = "10" or right({SPRICLIST.PLI_0},2) = "12" then
{ITMMASTER_ITM.ITMREF_0}
else
if right({SPRICLIST.PLI_0},2) = "15" then
{ITMMASTER_15.ITMREF_0}
else
{ITMMASTER_MKM.ITMREF_0}
................

Results, instead of the mentioned on the OP, I get:
Group----item----expected result
1---------A---------1
1---------B---------1
1---------C---------1
2---------A---------2
2---------B---------2
3---------A---------3
3---------C---------3
3---------D---------3
3---------B---------3

Seems it is counting by the group (which because it is the item itself, might be making the whole thing like a catch 22)

The Running Total is located on both details (which usually are suppressed but was testing it to see if any values changed) and on the Group Footer for a formula to get the item's formula results. This is the formula that's the group's origin (which in turn will be also the item itself):

local stringvar item:={@Product_Item};

.................

Reason for the formula was to use the "item" variable to store each item resulting from the function and then use it to try and find duplicates.

Because this is the Group, could be the reason why the RT isn't working properly?

EDIT - I've removed all grouping and tried it only using the report's detail section. Still am not getting far with this, can someone help me figure out how to find or count the duplicate data?

Thank you,

 

[pmax9999]

Without seeing the report it is a little difficult to troubleshoot but I think what you need is 5 separate running totals - 1 for each of the possible results of the Item formula.

If you post the report file with saved data I'd be happy to take a closer look.

Cheers

 

[Paulo R]

Hello,

I understand and am uploading a sample of the report (with only the relevant data on it).
And thank you for your help on this.

Report File

Cheers

 

[pmax9999]

I'm not certain I am going to be much help here as the report makes no sense to me.

The structure of the RT that you described in your original post is not how it is in your report. Additionally you are grouping (G5) on a formula that contains a variable whose value has been declared as the result of {@Product_Item} - why wouldn't you just group on the formula itself.

I would need to have a much better understanding of the data and what you are trying to achieve to be able to assist. Sorry!

Maybe someone else can help.

If you were able to provide a simplified data set with an explanation of the problem and required outcome I could provide some general guidance as to approach but I think that is the best I can do.

Regards

 

[Paulo R]

Hello,

Feel free to remove any and all groups, I'll work around that.

Just need help to figure out the duplicate data.

Thank you,

 

[Paulo R]

Managed to identify via "suppress if duplicated" the duplicate data (which came from a RT that couldn't be used in this fation).
Resorted to a running total + a formula to get the RT results.
If the formula repeats, get's suppressed.

Now I need to suppress the details when this happens (hiding the duplicate record data).

New Report File

Page 69, for example, we'll have 3 records repeated.

Can someone assist on this?

Thank you

 

[dgillz]

I looked at your report. I'd like to see the while report, your report only had 2 fields in it. I'd also like to see what your desired output is and understand why you have so many ITMMASTER* tables. Are these views or aliases or actual different tables?

Also how are they linked? Turn auto smart linking off and link them manually.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Paulo R]

Thank you but the report has that specific structure due to specifics from the software it's linked to related to pricing lists with multiple listings, different rules to determine price of model, or specific item, or marketing model, or customer model, or customer item, etc

All links were created manually and are correctly done.
But after rearranging the groups, reordering the report and using another field on the tables to order by priority, I've managed to solve this.

Thank you