topic Re: Conditionally display totals in QlikView

Conditionally display totals

paul_scotchford — Thu, 29 Aug 2013 03:50:57 GMT

I would like to conditionally turnoff totals on a straight table, can this be done at all ?

Scenario.

A table lists 4 rows , each row is by country and aggregated sales ... user selects rollup to a specific currency e.g. AUD$,

to the grand total of the 4 rows is in AUD and each row is converted to the select currency , however the user now selects

local currency, i.e each row total is in the local $ e.g. Canada, US , Aus etc.

The problem is the grand total is now summing different currencies, this is not desirable of course.

I cant find anyway that will allow me to conditionally switch off the Grand Totals when the user selects 'Local Currency' option.

Is there a chart property I can get access to in a script expression that will allow me to achieve this?

Thanks in advance

Re: Conditionally display totals

tresB — Thu, 29 Aug 2013 04:41:08 GMT

Have you considered using pivot table? May be dimensionality() function could help you there. If you feel that I have not understood your issue well, please post a sample here.

Re: Conditionally display totals

paul_scotchford — Thu, 29 Aug 2013 04:58:31 GMT

Thanks for the reply, the Pivot table idea has not been explored as its not spec'd for the dashboard, I'll try it and let you know the outcome.

Re: Conditionally display totals

paul_scotchford — Thu, 29 Aug 2013 05:17:55 GMT

Pivot table Is impractical in this instance as it will result in the incorrect format the user wants to see. I still need totals but only when a common currency is selected.

Re: Conditionally display totals

jagan — Thu, 29 Aug 2013 05:21:47 GMT

Hi,

Create 2 charts one with the total and another without total and then show and hide the charts based on the condition using Layout tab -> Show Conditional option.

Hope this helps you.

Regards,

Jagan.

Re: Conditionally display totals

Thu, 29 Aug 2013 05:27:38 GMT

try according to this attached example.

Regards

Re: Conditionally display totals

prabhuappu — Thu, 29 Aug 2013 13:34:45 GMT

Hi Paul,

I have created a sample application as per your requirement.

For Straight Table : I have added two expression. And Enabled expression total for one of the dimension. On click of a button I have Enabled one of the expression and hide the other one

For Pivot Table: I have added the same dimension twice. And Enabled Partial sums for one of the dimension. On click of a button I have Enabled one of the dimension and hide the other one

Make use of this and change the condition based on the currency selection...

Hop[e this helps you

Regards

Prabhu Appu

Re: Conditionally display totals

paul_scotchford — Thu, 29 Aug 2013 21:31:24 GMT

Hi Prabhu, Many thanks for your reply, this is a good idea, but the problem is that when many fields (in my case 15 fields) are displayed I would need to duplicate these , thanks anyway.

Re: Conditionally display totals

paul_scotchford — Thu, 29 Aug 2013 22:22:17 GMT

Further Notes .. thanks to everyone who has contributed so far, great efforts ...

Currency Options : $AU | $NZ | Local

Country Sales Summary

Country Item SalesLY SalesTY

$450 $625 <<<< Expression total , I want to hide this when local currency selected as summing on disparate currencies is incorrect

US Chips $US100 $US125

CAnada Chips $CA200 $CA225

NZ Chips $NZ100 $NZ175

AU Chips $AU50 $AU100

If the user selects the Currency to report all countries in, then they can choose $AU or $NZ then all sales totals are

converted to the reporting currency selected, then I expect the totals to display.

It would be great if QV supported conditional display of the Expression Total option i.e. Switch 'No Totals' or 'Exp Total' etc.

Re: Conditionally display totals

paul_scotchford — Thu, 29 Aug 2013 22:23:30 GMT

Thanks for your reply, not quite what I need, but a good reference for the future ...