Text box with IF function?

[poluvidyasagar]

Hi,

I am trying to achieve the following:

Part Num	Demand Day1	Demand Day2	DemandDay3	Balance
9006021	10	10	10	100
9064578	15	20	20	50
9048183	25	20	45	45
9086635	20	45	50	50
9065749	50	30	20	60
9012996	30	20	30	80
9028494	20	20	50	80
9018110	50	50	50	90
9078158	50	20	20	10

My Dimensions are:

PartNum

Balance

Variables:

vDays ( ranges between 1 and 3 because of 3 day demand)

Expressions:

1. Demand =

=if(vDays=1,DemandDay1,

if(vDays=2,DemandDay1+DemandDay2,

if(vDays=3,DemandDay1+DemandDay2+DemandDay3)))

2. Check

if(Balance<Demand,'Alert','OK')

Both these expressions worked OK. This is to test when a given part is going to be shortage. User can select number of days of demand using the input box of variable.

However, I would like to count the number of part shortages in text box based on user defined vdays. I have added the formula , however it does not seem to work. Check attached file.

Can someone help?

Thanks,
Vidya

[vishsaggi · Accepted solution]

Try this?

='Count of Part Shortages' &Chr(13)&

Count(DISTINCT if(Balance<if(vDays=1,DemandDay1,

         if(vDays=2,DemandDay1+DemandDay2,

             if(vDays=3,DemandDay1+DemandDay2+DemandDay3))), [Part Num]))

[poluvidyasagar]

Hi Vishwa,

It worked out.

However, I have another issue.

My Balance comes from a different file.

Balance:

Load * Inline [

Part Number, PackID, Qty

9006021, A1, 50

9064578, A2, 25

9048183, A3, 23

9086635, A4, 25

9065749, A5, 30

9012996, A6, 40

9028494, A7, 40

9018110, A8, 45

9078158, A9, 50

9006021, A10, 50

9064578, A11, 25

9048183, A12, 23

9086635, A13, 25

9065749, A14, 30

9012996, A15, 40

9028494, A16, 40

9018110, A17, 45

9078158, A18, 50

];

So i used Sum(qty) instead of Balance in text box. But it gave error saying that nested aggregation is not allowed.

How do i fix this?

Check attached.

Thanks,

Vidya

[vishsaggi]

Yes, you cannot use two aggr functions inside each other without aggregating on some dimensions so use something like:

='Count of Part Shortages' &Chr(13)&

Count(DISTINCT Aggr(if(Sum(Qty)<if(vDays=1,DemandDay1,

                        if(vDays=2,DemandDay1+DemandDay2,

                            if(vDays=3,DemandDay1+DemandDay2+DemandDay3))),[Part Number]), [Part Number]))

[poluvidyasagar]

Thanks Vishwa

It worked

[vishsaggi]

No Problem.