topic Totals using set analyais in QlikView

Totals using set analyais

Tue, 06 Oct 2015 09:35:13 GMT

Im trying to create a pie chart and need to total the dimension that I want to use

I have the following formula

(

sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))

This gives me the total of ALL PlannedCostsLCV0

I have a dimension called Activity and I want the formula to total PlannedCostsLCV for just the Activity dimension

Re: Totals using set analyais

rubenmarin — Tue, 06 Oct 2015 09:42:22 GMT

Hi aNdrew, maybe using a Dimension total por PlannedCostsLCV0:

(

sum(TOTAL <Activity> {$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))

If the pie dimension is already activity you can remove the TOTAL of PlannedCostsLCV0:

(

sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))

If you also want PlannedCostsLCV1 per activity you can apply the same options for the second part of the expression:

(

sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))

Re: Totals using set analyais

Digvijay_Singh — Tue, 06 Oct 2015 09:48:56 GMT

Something like this -

=Sum(aggr(sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV0),Activity)) - Sum(aggr(sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV1),Activity))

Re: Totals using set analyais

Tue, 06 Oct 2015 10:00:16 GMT

Thanks

That gave me the total of PlannedCostLCV0 - PlannedCostsLCV1 for each activity type, But what I need is the total against each activity type (106,439,351.2)

=if(left(Activity,4) = '8437' or left(Activity,6) = '843802' or left(Activity,6) = '843803', Activity)	% Variance (Plan 0 - Plan 1)	% Variance (Plan 0 - Plan 1)
	106,439,351.2	106,439,351.2
843703 Direct Acty Allocn Manf Support SVC Hrs	172,804.0	172,804.0
843710 PS Mechancial Engineering/Design	62,644,757.9	62,644,757.9
843711 PS Controls Engineering/Design	3,055,767.5	3,055,767.5
843712 PS Controls Programming	52,415.0	52,415.0
843713 PS Documentation	3,143,750.2	3,143,750.2
843714 PS Service Field support	5,399,332.2	5,399,332.2
843715 PS Project Management	30,291,165.2	30,291,165.2
843716 PS FAT/Install assist.	386,222.8	386,222.8
843721 Applications Engineering	219,016.7	219,016.7
843722 PS Procurement - Buyer/Planners	449,944.1	449,944.1
843723 PS Contract Administration	86,482.3	86,482.3
843724 PS Quality	521,807.2	521,807.2
843743 Direct activity allocn of Selling OS hrs	15,886.2	15,886.2

Re: Totals using set analyais

Digvijay_Singh — Tue, 06 Oct 2015 14:05:47 GMT

Can you share small sample with actual fields and output, not getting exact need..

Re: Totals using set analyais

Tue, 06 Oct 2015 14:23:28 GMT

I'm trying to create a pie chart that gives the percentage variance by activity type. I have activity type as a dimension and then in my expressions i'm trying to do the calc Planned costsV0 - Planned Costs V1 / Planned Cost V0 to give a percentage. What I'm currently getting though is a value that is the percentage per activity type and not a percentage of the total value of planned costs V0

So in the data example I sent I need to show :

% of Activity 843724 = 521,807 / 106,439,351 = 0.0049

At the moment Im just getting 100% as the formula is calculating 521,807 / 521,807

Re: Totals using set analyais

Tue, 06 Oct 2015 14:26:10 GMT

Hi Andrew,

Just wanted to share the Qlik Set Analysis Wizard tool. It's a life saver.

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

Hopefully it helps.

Will

Re: Totals using set analyais

rubenmarin — Tue, 06 Oct 2015 14:34:07 GMT

Hi Andrew, can you try this?:

(sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV1 ))

/ sum(TOTAL {$<IG_STATUS={'Open'}>} PlannedCostsLCV0 )

You can also use:

sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV0 ) - sum({$<IG_STATUS={'Open'}>} PlannedCostsLCV1 )

and check 'relative'

Re: Totals using set analyais

Digvijay_Singh — Tue, 06 Oct 2015 16:09:32 GMT

I tried to create sample data, see if we can link the actual need with this sample.