Cannot properly connect data. Set analysis issue

[MindaugasBacius]

Hei

There are two tables.

First table:

Amount	Date	Key
100	2016-01-30	1
200	2016-02-28	1
300	2016-03-31	2
50	2016-04-30	2

Second table:

Key	Date2	Value
1	2016-03-30	10
1	2016-03-31	20
1	2016-04-01	30
2	2016-03-30	15
2	2016-03-31	25
2	2016-04-01	35

1. Pick up all Amount fields where Date <= 2016-03-31.
2. Find the Date2 = 2016-03-31 and connect Value to the First table.
3. Make aggregation Amount * Value.

I was struggling to accomplish this task by using Set analysis.

Please find the qvw.

Thank you!

[MindaugasBacius]

Still cannot get it right.

I have modified the expression to:

=sum(Amount)/only({<Date = {"2016.03.31"}>} Value)

It do not calculate the Total value:

Total sum works while selecting the State:

How should I solve that?

Thank you in advance!

[Kushal_Chawda]

can you try this

sum(Amount)/Sum({<Date = {"2016.03.31"}>} Total <State>Value)

[MindaugasBacius]

Unfortunately:

[Kushal_Chawda · Accepted solution]

change total mode from "Expression total" to "Sum of Rows"

[Kushal_Chawda]

or try this

sum(Amount)/only({<Date = {"2016.03.31"}>} Total <State>Value)

[MindaugasBacius]

kush141087

You're good at this!

Thank you for your time.

[Kushal_Chawda]

Not a problem

[MindaugasBacius]

Both expressions return correct answers when changing from Expression Total to Sum of Rows.