5 Replies Latest reply: Mar 12, 2010 8:43 AM by louise.lindell

# How to create a gauge chart based on conditions

I have a very basic table with AccountType and Amount.

I want to create a gauge chart where the value is calculated as: Amount where AccountType = 'Vacancy' / (Amount where AccountType = 'Vacancy' + Amount where AccountType = 'Rented').

How can that be done?

Regards,

Staffan E.

• ###### How to create a gauge chart based on conditions

Hello Staffan,

You may use set analysis for that:

`Sum({< AccountType = {'Vacancy'} >} Amount) / Sum({< AccountType = {'Vacancy', 'Rented'} >} Amount)`

• ###### How to create a gauge chart based on conditions

Thank you very much for the help! It works great!

// Staffan

• ###### How to create a gauge chart based on conditions

Hi,

It works great for me too!! :-) - that is, I do get a result in my gauge charts when using the following set analysis (copied and somewhat modified from Miguel, above):

(Sum({< OrderSalesManNo = {'12'} >} InvoiceLineAmountNetLC)
-
Sum({< OrderSalesManNo = {'12'} >} SalesmanBudget))
/
Sum({< OrderSalesManNo = {'12'} >} SalesmanBudget)

However, I'm not sure the result is quite correct.... since I have a budget per salesman that is divided per week over the year (2010).
In another gauge chart I have the following set analysis (had some help there too) for comparing Actual to Budget for the company's total sales
and I only want to show the result Year to Date:
(sum({ 1 < Year = {\$(#=Only(Year(today())))} > } InvoiceLineAmountNetLC)
-
sum({ 1 < Year = {\$(#=Only(Year(today())))} > } if(%YearWeek <= right(Year(today()), 2) & num(Week(today()),00), BudgetInvoice)))
/
sum({ 1 < Year = {\$(#=Only(Year(today())))} > } if(%YearWeek <= right(Year(today()), 2) & num(Week(today()),00), BudgetInvoice))
How do I get the Date-condition from the second analysis into the first analysis? I've tried a few combinations, but none works.
I would be very grateful and happy if anyone can help me!!
Regards,
Louise

• ###### How to create a gauge chart based on conditions

Hello Louise,

There are several questions in your post. For all year to date calculations, I'm creating, in my master calendar, a flag field for current and previous year, with function

`LOAD ... YearToDate(DateField, -1) AS PreviousYTD, YearToDate(DateField) AS CurrentYTD ... FROM ...`
which returns "-1" for true and "0" for false, meaning that set analysis for year to date comparisons is as easy as
`Sum({< PreviousYTD = {-1}>} Amount)`

• ###### How to create a gauge chart based on conditions

Hello Miguel,

Thank you for your reply :-). However, I'm not sure it helped me very much.

On the other hand - I did manage to combine my two conditions, Date and Salesman, in the same Set analysis and that works very well now.
Example:

(sum({ 1 < Year = {\$(#=Only(Year(today())))}, OrderSalesManNo = {'11', '3', '4', '9', ' '} > } InvoiceLineAmountNetLC)

Best regards,
Louise