How to create a gauge chart based on conditions

[#1]

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.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Staffan,

You may use set analysis for that:

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

[#3]

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

// Staffan

[#4]

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

[Miguel_Angel_Baeyens]

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)

[#6]

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