Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlikians,
Please help me to get the total, stuck here from last 3 days.
My Variant cost is a connecter file
Please PFB SS
Hi, you have to expand your measure menu and scroll down to find the total function
then under Style you have to enable it
Hi,
Can you please share it in english.
Also i have that much of option.
In measure menu
In overall
use an aggregation function like Sum() or Avg() for measures
= SUM( Qty * [Variant Cost] )
will not get the perfect data total will come as the formula for the same will be Sum(Quantity)* [Variant Cost].
After applying this formula will not get the total.
SS for the same the data is coming correct in Sum(Quantity)* [Variant Cost], but the total is not coming
Sum(Quantity)* [Variant Cost] is different from Sum(Quantity * [Variant Cost] )
Or you can wrap your expressions in AGGr()
sum( AGGR( Sum(Quantity)* [Variant Cost] , Dim1, Dim2,...) )
Dim1,Dim2 is the list of dimensions used in the pivot chart
Yes I am saying the same to you if i sold 100 units and my Variant Cost is 10 then will apply the formula Sum(Quantity)* [Variant Cost] so that will get 1000 if i applied that formula the data will b wrong that's why i shared the SS of the same.
Thanks
KC
@vinieme12 is right. You cannot use a naked field reference like [Variant Cost] without getting NULL in the totals row.
So, you must wrap it in an aggregation function, or use Sum(Aggr(...)).
See also https://community.qlik.com/t5/Design/Use-Aggregation-Functions/ba-p/1475833
HIC
Not be able to getting proper answer can you please help me with formula
All of the following will return a value of the total row.
Sum(Quantity*[Variant Cost])
Sum(Quantity)*Avg([Variant Cost])
Sum(Aggr(Sum(Quantity*[Variant Cost]), Dim1, Dim2,...) )
Sum(Aggr(Sum(Quantity)*Avg([Variant Cost]), Dim1, Dim2,...) )
(Dim1,Dim2 is the list of dimensions used in the pivot table)
Which one to use depends on how your data looks: Since Quantity and [Variant Cost] are in two different tables, there is the possibility of a many-to-many relationship. If you know that one row in the fact table ALWAYS is linked to only one single value of [Variant Cost], you should use
Sum(Quantity*[Variant Cost])
If there can be several values, you should probably use
Sum(Aggr(Sum(Quantity)*Avg([Variant Cost]), Dim1, Dim2,...) )
HIC