Need help in set analysis expression

[musketeers]

Hi, I have below data as  a inline table load statement:

PROD_DATA:
Load Inline [Product,Returned/NotReturned,Reason,Quantity
P1, R, R1, 10
P2, R, R2, 20
P3, R, R3, 40
P4, R, R4, 30
P1, NR, , 50
P2, NR, , 40
P3, NR, , 60

P4, NR, , 70
]

Product	Returned/NotReturned	Reason	Quantity
P1	R	R1	10
P2	R	R2	20
P3	R	R3	40
P4	R	R4	30
P1	NR		50
P2	NR		40
P3	NR		60
P4	NR		70
P1	R	R2	50
P2	R	R5	20
P3	R	R1	40
P4	R	R2	30

From this data we have filter on Reason column. If user select a Reason say R1. We have two products P1 and P3 which are returned for reason R1. For other products the return reason is not R1. so the output is like below:

R/NR	Product	Count
R	P1	10
NR	P1	100
R	P2	0
NR	P2	80
R	P3	40
NR	P3	100
R	P4	0
NR	P4	130

For R1 reason product P1 has 10 returns. Hence 10 shown. for Not returned status, we should display total number of products minus (-) total return for selected reason. Hence 100 shown for product P1 and status Not returned. For product P2 there is no return for Reason R1 hence 0 shown and for Not Return status 80 as total count of P2 ie 80 minue (-) total return for reason R1 which is 0.

I have written ste analysis for return count ie sum({<Returned/NotReturned = {'R'}>}Quantity)

I am stuck with the Not Return count expression for this given requirement. I have written:

sum({<Returned/NotReturned = {'NR'},Reason>}Quantity)

This is wrong as this will count only those with status Not Returned. But what about those which are returned and not in selected reason. Those also I need to display in Not Return.

I need your expert advise how to acheive this requirement.

[Sayed_Mannan]

Thanks for the detailed explanation! You are very close to the solution.

lets break it down:-

The correct Returned Quantity Expressions should be :

Sum({<Returned/NotReturned = {'R'}, Reason = {'R1'}>} Quantity

The correct Not Returned Expression should be: 

Sum({
    <Returned/NotReturned = {'NR'}>
    +
    <Returned/NotReturned = {'R'}, Reason -= {'R1'}>
} Quantity

If you want to make it dynamic based on user selection, you need to use the P() function to get selected values:-

Sum({
    <Returned/NotReturned = {'NR'}>
    +
    <Returned/NotReturned = {'R'}, Reason -= P(Reason)>
} Quantity)

This way, it will always subtract the selected Reason(s) from the 'R' rows and include the rest.

I hope this helps.

Cheers,
Sayed Mannan Ahmad

[musketeers]

Hi Sayed, Thanks verymuch for your help. I tried this option but the issue is that the part of setanalysis :

 <Returned/NotReturned = {'R'}, Reason -= P(Reason)>

is returning 0 for NR rows and only return data for NR status.

[rafaelencinas]

Hi !

Lets check each point:

1 - Total Produtcs ( Return and Not Returned ) - ignore the Reason

Sum({<Reason=>}Quantity)

- When you select a Reason, will count / sum all produts

2 - Total Produtcs Returned

Sum({<Reason=,[Returned/NotReturned]={'R'}>}Quantity)

3 - Total Products Not Returned

Sum({<Reason=,[Returned/NotReturned]={'NR'}>}Quantity)

In your cenario, if you start selecting the Reason to analyze produtcs, its better to ignore the reasons. 

Bye!

[musketeers]

Hi Stallone, Thanks for your suggestion. My actual problem is the table chart visual which I need to create

R/NR	Product	Count
R	P1	10
NR	P1	100
R	P2	0
NR	P2	80
R	P3	40
NR	P3	100
R	P4	0
NR	P4	130

Here in the Returned rows, the set analysis

Sum({<Returned/NotReturned = {'R'}, Reason = {'R1'}>} Quantity)

shows the correct amount.

The problem is in Not Returned rows. When I try to calculate those returned product with reason other than R1, it is not allowing me to calculate for Not Returned rows.

Any help will be much appreciated.

Thanks

[Sayed_Mannan · Accepted solution]

Hi, I’ve made a few adjustments to the script. You can now use this single expression to get exactly the output you're looking for:

here is the modified script:

=If([Returned/NotReturned] = 'R',
    Sum({<Reason = {'R1'}>} Quantity),
    If([Returned/NotReturned] = 'NR',
        Sum({<Reason -= {'R1'}>} TOTAL <Product> Quantity)
    )
)

here is the output that I am getting:-

Let me know if this works for you or if you'd like any further tweaks!