Solved: Re: Return a value based on field's value from a d... - Qlik Community

amirkachlon · ‎2019-02-07

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

sunny_talwar · ‎2019-02-11

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))

View solution in original post

sunny_talwar · ‎2019-02-07

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?

jaibau1993 · ‎2019-02-07

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.

sunny_talwar · ‎2019-02-07

~~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?~~

jaibau1993 · ‎2019-02-07

@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.

sunny_talwar · ‎2019-02-07

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

jaibau1993 · ‎2019-02-07

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

Jaime.

sunny_talwar · ‎2019-02-07

Nice to meet you too Jamie.

amirkachlon · ‎2019-02-10

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

amirkachlon · ‎2019-02-10

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

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

Alternate States