Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to duplicate records for a table using NODISTINCT function but it is returning nulls. I have 2 dimensions which is page and its keyword associated to that page. The expression is keyword trend value which gives the keyword its popularity value, but does not associate on a page level. I have other expressions such as traffic and rank which associates page to their respective keyword. The problem I have is that the table is returning nulls in 'Keyword Trend Value' column when the keyword has a trend value that exists. I tried using this formula aggr(nodistinct(avg(keyword_trend_value),keyword).
This what the ideal output would look like:
Page | Keyword | Rank | Traffic | Keyword Trend Value |
---|---|---|---|---|
ABC.com | A | 3 | 20 | 25 |
ABC.com | B | 1 | 100 | 75 |
DEF.com | A | 5 | 60 | 25 |
LMN.com | B | 7 | 15 | 75 |
XYZ.com | C | 6 | 80 | 30 |
Thanks!
You are showing your expected result / chart, but how does your input tables in your model look like?
That's what I haven't really understood from your description.
You can probably get your required result using the TOTAL qualifier with a field list in your aggregation function, either after adding all chart dimensions to the aggr() function (and removing the nodistinct qualifiert), or maybe you don't need the aggr() function at all.
What about
=Avg(TOTAL<keyword> keyword_trend_value)
Hi Stefan,
This is what the input table looks like:
Page | Keyword | Clicks | Keyword Trend Value |
---|---|---|---|
ABC.com | A | 20 | 25 |
ABC.com | B | 100 | 75 |
ABC.com | C | 210 | - |
ABC.com | D | 456 | - |
As you can see, I will filter for ABC.com and some will show up as null when there is a trend value that exists for that keyword.
I used Avg(TOTAL<keyword> keyword_trend_value) and same problem occurs.
Could you upload a small sample QVW that demonstrates your issue?