Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amirkachlon
Contributor III
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
sunny_talwar

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

11 Replies
sunny_talwar

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
Partner - Creator III
Partner - Creator III

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

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
Partner - Creator III
Partner - Creator III

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

sunny_talwar

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

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

Jaime.

sunny_talwar

Nice to meet you too Jamie.
amirkachlon
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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