Solved: Avoiding aggregation in qlikview chart - Qlik Community

deepakqlikview_123 · ‎2017-01-31

Hi,

How should I prohibit aggregation in qlikview chart level as in Qlikview expression aggr function is mandatory but when I am using Rowno for creating separate columnin backend and using same col in chart and hiding that it is giving me eoor like out of memory object.

Can you please suggest how should i tackle on this issue.

Thanks

jayanttibhe · ‎2017-02-07

Hi Deepak,

You trying to aggregate at "Granular level" than the KEY field used which is not possible in any case.

Ultimately you want the FACT calculations on the actual ROWs which are presentin the FACT table. In this case, even though you are using the Straight Table, in the expression tab DONT use any aggregation function. i.e.

instead of -

(sum(OpenAR_USD)/FX_CONV_RT)/ScaleValue

You can simply use : - Dont use any Aggregation Function.

((OpenAR_USD)/FX_CONV_RT)/ScaleValue

Now, this will work in all the cases except when you have all the Dimensions in the Straight Table with SAME set of Values. In that case you ll get NULLs as Qlik cannot differentiate which measure (Expression value ) you want to Pick on the single set of dimensions. In this case you need a lower (Lowest in the case - As you wanted ALL the Rows from Fact Table) ; So Rowno() makes sense to me and then hiding this column from your straight table.

I would rather suggest use Recno() as you want the counter on your INPUT data and not the output data.

Basically, if want to show data on LOWER level than the actual Chart dimensions, then you need to pull that dimension in the Chart whether that's hidden dim or visible Dim - it should be present in the chart.

Also, I think dimensionality() function is not needed as it makes sense for Pivot Tables only.

Thanks

Jayant

View solution in original post

sunny_talwar · ‎2017-01-31

Not sure I understand what you have done.... What is your Aggr() expression look like?

sdmech81 · ‎2017-01-31

Rather keep one expression say: =sum(1) and hide that.

and add only dimensions that are needed..

Sachin

deepakqlikview_123 · ‎2017-01-31

I am using sum(Expression)/Currecy conversion in expression but due to this i am able to get all sum

Rather than this i need to show all it is rather than performing sum()

Suppose I have 2 columns

A B

a 1

a 2

Then I have to show whole data as it is rather than showing this ijn straight table

A B

a 3

I Thanks

vinieme12 · ‎2017-01-31

Qlikview expression aggr function is mandatory

NO it's not, please post sample app

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

deepakqlikview_123 · ‎2017-01-31

Then how should i perform

if(Dimensionality() <> 0, (sum(CreditLimit_USD)/FX_CONV_RT)/ScaleValue)

Without aggr

vinieme12 · ‎2017-01-31

please post a sample app, this exp does not need aggr until you want SUM(AGGR(SUM(....

or you are doing this on a disconnected dimension

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

deepakqlikview_123 · ‎2017-01-31

Hi All,

Please find attached sample app.

I need to avoid the aggregation in expresion but rowno() gives performance issues for larger data sets.

Can you please suggest how should i avoid aggregation.

Thanks !

sunny_talwar · ‎2017-01-31

You don't want to use KeyRow in your chart, but still want to see each and every single row? Is that what you are wanting to do??

deepakqlikview_123 · ‎2017-01-31

Yeah sunny,

Actually I am using same Keyrow in my chart but it is giving me out of memory issue and not able to display data.

Thanks