Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Logic help needed

May be this:

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


Capture.PNG

Not applicable

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

Not applicable

Re: Logic help needed

If it is simple transformation then I would not mind

Not applicable

Re: Logic help needed

Also when I select the ID from listbox value becomes 0

Re: Logic help needed

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

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)


Capture.PNG

Not applicable

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

 

IDValue
10
20
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?