topic Re: Return a value based on field's value from a different alternate state in QlikView

Return a value based on field's value from a different alternate state

amirkachlon — Sat, 16 Nov 2024 04:31:56 GMT

Hi All,

appreciate your assistance

Current State:

I have a table of transactions per supplier per quarter, as described below:

Quarter	Transaction Date	Supplier Code	Item Number	Cost ($)	Last Transaction of the Quarter
Q1'18	24/07/18	Supplier 1	AAAA	$10,235.17	0
Q1'18	26/07/18	Supplier 2	AAAA	$8,920.00	1
Q2'18	24/10/18	Supplier 2	AAAA	$8,920.00	0
Q2'18	25/10/18	Supplier 1	AAAA	$9,911.81	1

The green lines are the last transactions per quarter (with no reference to supplier).

I use two alternate states to show the last transactions per quarter, so when the user choose Q1'18 in [alternate state 1]

and Q2'18 in [alternate state 2] he will get the two green lines as a result.

Challenge:

Knowing that the last transaction for Q2'18 in [alternate state 2] is from Supplier 1, I wish to find the last transaction for Supplier 1 for Q1'18 in [alternate state 1].

meaning, in the example the result will be the transaction marked in blue.

Quarter	Transaction Date	Supplier Code	Item Number	Cost ($)	Last Transaction of the Quarter
Q1'18	24/07/18	Supplier 1	AAAA	$10,235.17	0
Q1'18	26/07/18	Supplier 2	AAAA	$8,920.00	1
Q2'18	24/10/18	Supplier 2	AAAA	$8,920.00	0
Q2'18	25/10/18	Supplier 1	AAAA	$9,911.81	1

End Result:

The marked green calculation are available in my solution what I'm missing is the calculation described above marked in red

When I choose a specific item it works, but when no item is selected it seems that its not calculated properly

Item Number	Alternate State 1 Transaction Cost	Alternate State 2 Transaction Cost	Alternate State 2 Supplier Code in Alternate State 1 Quarter
AAAA	$8,920.00	$9,911.81	$10,235.17
BBBB	$7,920.00	$9,911.81	$8,235.17

Hope to find a helper : )

Thanks

Amir

Re: Return a value based on field's value from a different alternate state

sunny_talwar — Thu, 07 Feb 2019 13:29:49 GMT

It will be easier to help if you are able to provide a sample qvw where we can see what you have and use that to test it out. Are you able to share a sample qvw file?

Re: Return a value based on field's value from a different alternate state

jaibau1993 — Thu, 07 Feb 2019 16:02:42 GMT

Hi Amir!

Since my solution is harder being explained than being showed, I refer you to the attached document. Hope it helps! (I also hope to have undertood you 🙂 )

Bests,

Jaime.

Re: Return a value based on field's value from a different alternate state

sunny_talwar — Thu, 07 Feb 2019 16:24:58 GMT

~~When I opened up your dashboard I see this~~

~~Isn't this what you mentioned you wanted? What is wrong with the output we see?~~

Re: Return a value based on field's value from a different alternate state

jaibau1993 — Thu, 07 Feb 2019 16:21:49 GMT

@sunny_talwar wrote:
When I opened up your dashboard I see this

Hi Sunny. This is my solution to Amir's problem, not a data sample from Amir.

Jaime.

Re: Return a value based on field's value from a different alternate state

sunny_talwar — Thu, 07 Feb 2019 16:25:34 GMT

My bad, I feel embarrassed. Sorry for not realizing that you are a different person.

Re: Return a value based on field's value from a different alternate state

jaibau1993 — Thu, 07 Feb 2019 16:28:26 GMT

Hahahaha no problem Sunny. Nice to meet you, by the way.

Jaime.

Re: Return a value based on field's value from a different alternate state

sunny_talwar — Thu, 07 Feb 2019 16:35:24 GMT

Nice to meet you too Jamie.

Re: Return a value based on field's value from a different alternate state

amirkachlon — Sun, 10 Feb 2019 10:06:37 GMT

hi Jamie,

Thanks for your reply !

Like i mentioned it works great for one item, once you have more than one item the "vlookup" doesnt work properly.

please see attached your solution with another item BBBB, to show the issue.

Thanks,

Amir

Re: Return a value based on field's value from a different alternate state

amirkachlon — Sun, 10 Feb 2019 10:08:48 GMT

Hi Sunny,

Thanks in your interest in solving my issue.
please use the file i attached below the one Jamie kindly created, added a bit more data

Thanks,
Amir

Re: Return a value based on field's value from a different alternate state

sunny_talwar — Mon, 11 Feb 2019 12:38:00 GMT

Try this expression

=Sum({<Quarter = Alt1:: Quarter, Meh = {0}>} Aggr(
  If(Supplier = Only(TOTAL <Product> Aggr(If(Date = Max(TOTAL <Product> {<Quarter = Alt2::Quarter>} Date), Supplier), Product, Date))
  , Sum(Amount))
, Product, Supplier, Date))

Re: Return a value based on field's value from a different alternate state

amirkachlon — Thu, 07 Mar 2019 14:10:16 GMT

Great, what i was missing is the use of the total by product. Great solution thanks