topic Re: Logic help needed in QlikView

Logic help needed

Mon, 26 Jan 2026 18:19:17 GMT

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

sunny_talwar — Tue, 30 Aug 2016 09:54:36 GMT

May be this:

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

Re: Logic help needed

Tue, 30 Aug 2016 10:03:32 GMT

Would it be possible with set analysis?

Re: Logic help needed

sunny_talwar — Tue, 30 Aug 2016 10:05:00 GMT

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

Re: Logic help needed

Tue, 30 Aug 2016 10:07:45 GMT

If it is simple transformation then I would not mind

Re: Logic help needed

Tue, 30 Aug 2016 10:13:29 GMT

Also when I select the ID from listbox value becomes 0

Re: Logic help needed

sunny_talwar — Tue, 30 Aug 2016 10:18:57 GMT

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)

Re: Logic help needed

sunny_talwar — Tue, 30 Aug 2016 10:23:56 GMT

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

Tue, 30 Aug 2016 10:25:45 GMT

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

sunny_talwar — Tue, 30 Aug 2016 10:28:11 GMT

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

Tue, 30 Aug 2016 10:32:20 GMT

Yes, Input will be like below

Step

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

Re: Logic help needed

sunny_talwar — Tue, 30 Aug 2016 10:45:29 GMT

Try 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 *

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

Thu, 01 Sep 2016 18:09:30 GMT

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

sunny_talwar — Thu, 01 Sep 2016 18:25:40 GMT

Can you share how your ID looks like?

Re: Logic help needed

Thu, 01 Sep 2016 18:29:55 GMT

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

Re: Logic help needed

sunny_talwar — Thu, 01 Sep 2016 18:56:27 GMT

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

Fri, 02 Sep 2016 07:59:41 GMT

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

Fri, 02 Sep 2016 08:01:36 GMT

please help stalwar1‌, swuehl‌ gwassenaar‌

Re: Logic help needed

Fri, 02 Sep 2016 10:26:49 GMT

any update?

Re: Logic help needed

sunny_talwar — Fri, 02 Sep 2016 10:47:38 GMT

Responded to your new thread:

Re: Previous value logic