Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Logic help needed

I have below data set.

 

IDSubIDActual
11100
12200
13250
22300
23400
33500
34600

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

 

IDValue
10
2200
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

20 Replies
sunny_talwar

May be this:

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


Capture.PNG

Not applicable
Author

Would it be possible with set analysis?

sunny_talwar

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

Not applicable
Author

If it is simple transformation then I would not mind

Not applicable
Author

Also when I select the ID from listbox value becomes 0

sunny_talwar

May be like this:

Table:

LOAD *,

  AutoNumber(ID&SubID) as Key;

LOAD * INLINE [

    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)

Capture.PNG

sunny_talwar

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)


Capture.PNG

Not applicable
Author

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

 

IDValue
10
20
3

250

How would I do it in front end?

sunny_talwar

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?