Using aggr() and count

Anonymous — Mon, 11 Sep 2017 13:22:25 GMT

Hi,

Im having trouble calculating the following dataset due to not be able to certain functions in dimensions such as the sum() function.

This table is my dataset.

ProjectName	Funding	Program	Budget	Actual
Project1	1	A	100	30
Project1	2	A	100	20
Project2	1	A	200	30
Project3	1	B	300	50
Project3	2	C	300	200
Project3	3	C	300	10
Project4	1	A	200	40
Project4	2	A	200	30
Project5	2	A	100	50

I want to be able to put the data in a bar chart using the dual() function to calculate the percentage of Actual against the Budget but I cant total the actual column due to being able to use the sum() function in the dimension. Is there an aggr() I could use or other function or load method to sum the Actual value for each specific project.

This is the dimension I want to use but cant. if(sum(Actual)/Budget > 1.5, Dual('150% & Greater',1).

The following code is where I am currently.

Dimension=if(Actual/Budget > 1.5, Dual('150% & Greater',1),

if(Actual/Budget > 1.25, Dual('125% - 150%',2),

if(Actual/Budget > .75, Dual('75% - 125%',3),

if(Actual/Budget >= .50, Dual('50% - 75%',4),

if(Actual/Budget < .50, Dual('Below 50%',5)

)))))

Measure = Count(Distinct ProjectName)

Re: Using aggr() and count

swuehl — Mon, 11 Sep 2017 15:18:43 GMT

Try as calculated dimension

Aggr( if(sum(Actual)/Budget > 1.5, Dual('150% & Greater',1), ProjectName)

edit: replace the if() with whatever you need to classify your aggregated values

topic Using aggr() and count in App Development

Using aggr() and count

Re: Using aggr() and count