Create Table or Field on-the-fly After a Filter Applied

[#1]

Hi QV Experts! .... and Master Sokkorn Cheav

I have this table:

SOURCE TABLE

Period	City	Score	Weight
2010	Jakarta	12	15%
2010	New York	30	25%
2010	Melbourne	5	30%
2010	Madrid	58	10%
2010	Tokyo	15	10%
2010	Prague	6	10%
2011	Jakarta	22	10%
2011	New York	29	25%
2011	Melbourne	4	10%
... and so on	... and so on	... and so on	... and so on

I want to create table below (which is always updated by script or something) after filter for PERIOD applied:

In example below, filter PERIOD = 2010 applied.

ON-THE-FLY TABLE

Rank	Period	City	Weighted Score = (Score * Weight) * Average({$<City=>} Score) * 100
3	2010	Jakarta	8.57
1	2010	New York	35.7
4	2010	Melbourne	7.14
2	2010	Madrid	27.6
5	2010	Tokyo	7.14
6	2010	Prague	2.85

How can I do this within QV:

1. Run the calculation and create table on-the-fly after filter period applied
2. Automatically give rank based on weighted score

Please help. Thank you.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw

[#3]

Got it! Thank you very much Gysbert

[Sokkorn]

Dear Mr. Adrian,

Ahh I missed this post since earlier.

Gysbert give you the best method. And here is a new one that can work around.

Let try creat two expression

1. Weighted Score

    Label: Weighted Score

    Definition: Sum(Score*Weight)/Avg(Total {$<City=>} Score)

2. Rank

    Label: Rank

    Definition: Rank([Weighted Score])

This is just another solution for your to consider.

Regards,

Sokkorn

[#5]

Thanks Sokkorn!

Gysbert answer it really well.

I just need the idea to create a chart with type = STRAIGHT TABLE and then add some expressions on it.

Well I think QV should differs STRAIGHT TABLE from chart because it is a little bit confusing when you need to build one.