Forum Discussion

TaoW's avatar
TaoW
Regular Visitor
1 year ago
Solved

Question about the RANKX and accumulated number

Hi , guys  I met some problems about the "rankx"function and  calculating accumated number.   Dimension table: Product (RPN, Divison)       Fact table :Sales( Productrpn，Sales) Relationship:     ...
Need Help
TaoW's avatar
TaoW
Regular Visitor
1 year ago

Thanks for your reply.  But in the real situation,those rpns which have no sales records didn't exsit in the fact table. in your example, "C“”H“"K""P"shouldn't be in the table2 . RPNs qty> productrpn qty. You can't sell all kinds of RPNs in several years. So when i wanted to rank them,
1)if i use the "RPN"field, i had to face those blank lines as i showed in the question1.
2)if i use the field "productrpn", i must use "SUMMARIZE" to create a new table for ranking (use "crossjoin"will also face the same problem:blank lines join in the ranking).In this situation, "productrpn"&"Division"are from different dimensions, the calculation of accumulated number will become complicated.   
* I try to use the "runningsum"and "windows" functions to calculate it directly but all failed. Under the traditional way, i don't know how to write among multiple fields which are from different tables.