Discussion Board for collaboration related to Creating Analytics for QlikView.
I have 2 tables : 1 is the ciity table contains City, State_ID , Population, GRP on City Level
2 is the State table.
Then the final pivot table I have to make is shown as :
State, GRP on State level,
To Calculate the GRP on State Level - I have to use the set analysis which I am no master of.
GRP on State Level= (GRP on City Level * Population on City level) / Total Population on State Level)
Total Population on State Level= Total Population on City 1+2+... for every citry belong in that state.
Any help will be appreciated.
Go to Solution.
I've made a solution using the both fields from both tables (Year-City) vs (Brct_Year-City) if you have a problem with the Brct_Year field this is a possible way.
I hope this helps you. Let me know if this solution isn't right.
I don't know why you are trying to do it using set analysis. Here you are a possible solution using aggr function.
I hope this helps you, if you have any doubts, please, let me know it.
I really hope that I can do it with the agregate function - but the client want it in pivot table
in this format : State Name, GRP in state level
As you know I have to do calculation on the fly for GRP in the state level
Could you modify my example? this will help me to understand what you want.
Thanks very much for the help.
I thought - I misread the calculation. Your file really help me a lot.
I have the different year for the city population -- that what thru me off . And there is dimension called "Broadcast Year" which I have to link it to this year.
I did not rename the column the same in the qlikview- because it create unneccessary loop.
That's the whole reason I stuck with it.
I upload your file with the twist to my existing rpoblem- hope you can take a look
Thanks Miguel - this is good.
The only small problem is :
FinalT1: Load Brct_Year, City as CityT1, Brct_Year & City as KeyT1, State, [GRP on City Level] resident Table1;
The table : Table1 is so large and contains 450 millions records. just the second load alone will cause me double the load. To load the table1 it took almost 5 hours.