Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
amirkachlon
New Contributor III

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

Hi All,

appreciate your assistance

Current State:

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

QuarterTransaction DateSupplier CodeItem NumberCost ($)Last Transaction of the Quarter
Q1'1824/07/18Supplier 1AAAA$10,235.170
Q1'1826/07/18Supplier 2AAAA$8,920.001
Q2'1824/10/18Supplier 2AAAA$8,920.000
Q2'1825/10/18Supplier 1AAAA$9,911.811

 

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.

QuarterTransaction DateSupplier CodeItem NumberCost ($)Last Transaction of the Quarter
Q1'1824/07/18Supplier 1AAAA$10,235.170
Q1'1826/07/18Supplier 2AAAA$8,920.001
Q2'1824/10/18Supplier 2AAAA$8,920.000
Q2'1825/10/18Supplier 1AAAA$9,911.811

 

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 NumberAlternate State 1 Transaction CostAlternate State 2 Transaction CostAlternate 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)
1 Solution

Accepted Solutions

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

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

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

image.png



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

Jaime.

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

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

Nice to meet you too Jamie.
amirkachlon
New Contributor III

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

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