topic Re: To show a static total in a chart in QlikView

To show a static total in a chart

josephinetedesc — Sat, 28 Mar 2015 12:56:59 GMT

what I want is to allow users to select car type = Fords, but the total not to change ie to be static at 52 for example for the month of Jan. At present I can get the total but if the users select Fords -it shows the Fords total, I want to see the Fords total in relation to the Total of all cars sold.

Thank you

Month	1/01/2015	1/02/2015	1/03/2015	1/04/2015
Fords	30	40	30	40
Holdens	20	30	20	30
Kias	2	5	6	4
total	52	75	56	74

Re: To show a static total in a chart

MK_QSL — Sat, 28 Mar 2015 14:02:14 GMT

Sample Data:

Load * Inline

[

Car, Month, Sales

FORD, Jan, 100

FORD, Feb, 120

FORD, Mar, 90

HOLDENS, Jan, 120

HOLDENS, Feb, 80

HOLDENS, Mar, 110

KIAS, Jan, 120

KIAS, Feb, 70

KIAS, Mar, 100

];

Create a Pivot Table

Dimension

Car

Month

Expression

IF(Dimensionality() = 0, Sum({1}Sales), SUM(Sales))

Re: To show a static total in a chart

fred_s — Sat, 28 Mar 2015 14:27:36 GMT

Hi Josephine,

Check the attachment.

It's a different approach, but maybe you like it.

Grtz Fred

Re: To show a static total in a chart

josephinetedesc — Sun, 29 Mar 2015 00:31:28 GMT

Thank you Mannish I will need to check out dimensionality.

Re: To show a static total in a chart

josephinetedesc — Sun, 29 Mar 2015 00:32:48 GMT

Thank you Fred

I am at home so I have a limit to the number of files I can open (5???). At work tomorrow I will look at the file to see the set analysis used.

Re: To show a static total in a chart

josephinetedesc — Sun, 29 Mar 2015 00:34:52 GMT

Am I correct in the first instance the Dimension is Sales?

Re: To show a static total in a chart

josephinetedesc — Sun, 29 Mar 2015 00:42:33 GMT

Also Fred the gauge would not work for me - because the improtat thing is to see how well each carmake is perorming againsst the total of all cars sold. Cannot wait to getto work tomorrow 😕

Re: To show a static total in a chart

josephinetedesc — Sun, 29 Mar 2015 07:45:03 GMT

Sum({1} [NumSold])

This seems to work ... will there be a problem with this formula?? I changed the figures ...could not wait to get to work- curiosity was driving me insane

Re: To show a static total in a chart

MK_QSL — Sun, 29 Mar 2015 07:55:44 GMT

Provide sample data in excel file along with the result you are looking for.

Re: To show a static total in a chart

josephinetedesc — Sun, 29 Mar 2015 10:34:07 GMT

done ... Thanks for your help - obviously done late at night when not thinking clearly!!!!

Re: To show a static total in a chart

MK_QSL — Sun, 29 Mar 2015 10:46:18 GMT

Where is Month/Date fields? What result you are expecting?

Check enclosed file. Is this what you are looking for?

Re: To show a static total in a chart

fred_s — Sun, 29 Mar 2015 13:02:21 GMT

Hi Josephine,

I believe the Gauge does exactly what you want to achieve:

..how well each carmake is performing against the total of all cars sold..

Re: To show a static total in a chart

josephinetedesc — Sun, 29 Mar 2015 23:12:56 GMT

I ended up using

Sum({$<CarMake={'Fords','Holdens','Kias'}>}NumSold)

the best tutorial ever was by Michael Tarallo:

https://www.youtube.com/watch?v=YMQJnKMkfxg

https://www.youtube.com/watch?t=171&v=nB-HJ8X1QbI