Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
I was struggling to accomplish this task by using Set analysis.
Please find the qvw.
Thank you!
change total mode from "Expression total" to "Sum of Rows"
HI
I notice you have two date2 values of 31/03/2016 in table 2 one for key value 1 and the other for key value 2. Would it not be best to make a unique field from key and date in table 1 to associate to the same unique key made up from key and date2 in table 2 and then you should be able to calculate the correct sum of amount*value.
Kindest Regards
Brian
Hi Mindaugas,
Date and Date2 should not be associated?
In point 2 When you select 2016-03-31 in Date 2 you want all values for the key of first table only or the value for the key/date combination of the first table?
Maybe you need to combine the key and date in both tables and associate data by that composite key only.
Please let me know if this helps.
Kind regards,
what is expected output?
The result:
Amount | Date | Key | Value | Aggr |
---|---|---|---|---|
100 | 2016-01-30 | 1 | 20 | 2000 |
200 | 2016-02-28 | 1 | 20 | 4000 |
300 | 2016-03-31 | 2 | 25 | 7500 |
Total | 13500 |
Amount | Date | Key | Value | Aggr |
---|---|---|---|---|
100 | 2016-01-30 | 1 | 20 | 2000 |
200 | 2016-02-28 | 1 | 20 | 4000 |
300 | 2016-03-31 | 2 | 25 | 7500 |
Total | 13500 |
Sum of amount is correct:
SUM({< Date = {"<=$(=date(var_date))"}>}Amount), but you can't show Date field.
Date2 value -> Max({<Date2 = {"<=$(=date(var_date))"}, Key = P({<Date = {"<=$(=date(var_date))"}>})>}Date2)
May be this??
Regards!
Hi Mindaugas:
See attached file plz.
Regards.
=SUM({<Date={"<=$(=date(var_date))"}>}Amount) * Only({<Date2={"$(=date(var_date))"}>}Value)