# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

New Contributor III

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

Hi All,

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

Labels (1)
• ### Alternate States

1 Solution

Accepted Solutions
MVP

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

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))`
11 Replies
MVP

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

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?

Partner

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

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.

Highlighted
MVP

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

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?

Partner

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

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

MVP

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

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

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

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

Jaime.

MVP

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

Nice to meet you too Jamie.
New Contributor III

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

hi Jamie,

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

New Contributor III

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

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