Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
tamilarasu
Champion
Champion

Values On Row Total

Hi All,

   I have an excel file which filled with Region, Country and Target data. I need to show target value for region wise (from excel source) like below. I have used dimensionality() function but I could not achieve what I need. Instead of banging my head, I just posted here.

Capture.PNG

I have attached sample file. Any help would be much appreciated.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

This?

If(Dimensionality()=0, Only({<Region={'Over all'}>}Target),

If(Dimensionality()=1, Aggr(Only({1-<RegionId={'*'}>}Target),Region),

Sum({<RegionId={'>0'}>}Target))

)

View solution in original post

9 Replies
tresesco
MVP
MVP

Try:

If(Dimensionality()<=1, Avg({<RegionId={'>0'}>}Target),Sum({<RegionId={'>0'}>}Target))

tamilarasu
Champion
Champion
Author

Hi Tresesco,

The above expression will show average for the specific region. But, my requirement is different. I need to show region wise target which is already defined (from the above attached excel file). We have Country target and region target separately.  Check the below screenshot

Capture.PNG

Kushal_Chawda

try this

If(Dimensionality()<=1, avg(Target),Sum(Target))

Capture.JPG

tamilarasu
Champion
Champion
Author

Hi Kush,

As I already mentioned, I need to show defined region wise target (see excel file) and not average of the countries. Do you know any idea.!!

tresesco
MVP
MVP

This?

If(Dimensionality()=0, Only({<Region={'Over all'}>}Target),

If(Dimensionality()=1, Aggr(Only({1-<RegionId={'*'}>}Target),Region),

Sum({<RegionId={'>0'}>}Target))

)

tamilarasu
Champion
Champion
Author

Great and Thanks!!

Kushal_Chawda

great. tresesco the champ

Not applicable

Try this expression,

=If(RowNo()= 0 , Sum(RegionTarget), Sum(Target))

Not applicable

in presentation tab select show partial sum for Region, country, region id

and select Capture.PNGsubtotals on top