20 Replies Latest reply: Sep 2, 2016 7:18 AM by Vineeth Pujari

# Logic help needed

I have below data set.

 ID SubID Actual 1 1 100 1 2 200 1 3 250 2 2 300 2 3 400 3 3 500 3 4 600

Output needed (I want to calculate it in front end only)

 ID Value 1 0 2 200 3 400

Logic: For ID 2, I want to see previous ID & subid should be equal to current id

example, For ID 2, I will see the previous ID 1 and Pick subid =2 value

• ###### Re: Logic help needed

May be this:

=Sum(Aggr(If(SubID = Above(SubID) and ID = SubID, Above(Actual)), SubID, ID))

• ###### Re: Logic help needed

Would it be possible with set analysis?

• ###### Re: Logic help needed

Unless you are willing to make changes in the script, I doubt set analysis is possible here

• ###### Re: Logic help needed

If it is simple transformation then I would not mind

• ###### Re: Logic help needed

Also when I select the ID from listbox value becomes 0

• ###### Re: Logic help needed

You can use this expression to address the issue of getting zero when you select an ID

=Sum(Aggr(If(Only({1}SubID) = Above(Only({1}SubID)) and Only({1}ID) = Only({1}SubID), Above(Only({1}Actual))), SubID, ID)) * Avg(1)

• ###### Re: Logic help needed

May be like this:

Table:

AutoNumber(ID&SubID) as Key;

ID, SubID, Actual

1, 1, 100

1, 2, 200

1, 3, 250

2, 2, 300

2, 3, 400

3, 3, 500

3, 4, 600

];

Left Join (Table)

LOAD ID + 1 as ID,

SubID,

Actual as Prev_Actual

Resident Table

Where ID + 1 = SubID;

And then just this as your expression -> Sum(Prev_Actual)

• ###### Re: Logic help needed

Hi sunny,

but my requirement is dynamic. lets say currently I am showing for previous but there would be previous to previous and up to 5 step back. So when I will select the step as 1 above calculation will work and when I will select step 2 below will be output

 ID Value 1 0 2 0 3 250

How would I do it in front end?

• ###### Re: Logic help needed

Where is the input coming from? Are there set number of inputs like Step 1 to Step 10? Also, are we discarding the backend solution and moving onto just the front end solution?

• ###### Re: Logic help needed

Yes, Input will be like below

Step

1

2

3

4

5

based on step selection, our calculation should work. I think this is possible only from front end.

• ###### Re: Logic help needed

Try this:

Table:

AutoNumber(ID&SubID) as Key;

ID, SubID, Actual

1, 1, 100

1, 2, 200

1, 3, 250

2, 2, 300

2, 3, 400

3, 3, 500

3, 4, 600

];

Left Join (Table)

Where ID = SubID;

LOAD ID + IterNo() as ID,

SubID,

Actual as Prev_Actual,

IterNo() as Step

Resident Table

While IterNo() <= 5;

When Step = 2

When Step = 1

• ###### Re: Logic help needed

stalwar1 thanks a lot, but issue with me is, ID is actually in characters(iterno will not work i guess), I would not be able to share the data due to which I prepared this data. Sorry for the inconvenience.

• ###### Re: Logic help needed

Can you share how your ID looks like?

• ###### Re: Logic help needed

It's Ab12ef, abce12, cd12ce etc.. it's random alphanumeric string in which there is no pattern

• ###### Re: Logic help needed

So then what is Step1 and Step2? I guess you will have to provide a better sample for us to look at, this is something I can't really comment about without looking at

• ###### Re: Logic help needed

See this sample Data

 ID SubID Actual AB12CD AB12CD 100 AB12CD TD123C 200 AB12CD PC12KT 250 TD123C TD123C 300 TD123C PC12KT 400 PC12KT PC12KT 500 PC12KT KC123T 600

for Step 1, Go to previous ID(1 step),  check subid which is equal to below(1 step) id

 ID Value AB12CD 0 TD123C 200 PC12KT 400

for step 2, Go to previous ID(2 step),  check subid which is equal to below (2 step) id

 ID Value AB12CD 0 TD123C 0 PC12KT 250

Logic is the same which I have explained in OP.

• ###### Re: Logic help needed

any update?

• ###### Re: Logic help needed

Re: Previous value logic

• ###### Re: Logic help needed

You can still use the Original solution which stalwar1 provided

Use Autonumber on your ID and SubID fields this will create number ID fields that you had originally provided and proceed with Sunny's solution

LOAD  *,AutoNumber(ID) as IDKey,AutoNumber(SubID) as SubIDKEY INLINE [

ID,SubID,Actual

AB12CD,AB12CD,100

AB12CD,TD123C,200

AB12CD,PC12KT,250

TD123C,TD123C,300

TD123C,PC12KT,400

PC12KT,PC12KT,500

PC12KT,KC123T,600

];