Gauge Chart Last Month and Current Month

[#1]

Hey guys.

Im tryna create a Gauge Chart tht shows last mnth and current month sales comparison in %. i have no dimension. My expression is this:

=((sum( {$<CurYTDFlag = {'1'}>} Data)) -

sum({<MonthYear={">=$(=date(yearstart(today(),-1),'MMM-YYYY')) <=$(=date(addmonths(today(),-13),'MMM-YYYY'))"}>} Data))/

sum({<MonthYear={">=$(=date(yearstart(today(),-1),'MMM-YYYY')) <=$(=date(addmonths(today(),-13),'MMM-YYYY'))"}>} Data)

And in the presentation tab, under gauge settings, min value is this:

=(((sum( {$<CurYTDFlag = {'1'}>} Data)) -

sum({<MonthYear={">=$(=date(yearstart(today(),-1),'MMM-YYYY')) <=$(=date(addmonths(today(),-13),'MMM-YYYY'))"}>} Data))/

sum({<MonthYear={">=$(=date(yearstart(today(),-1),'MMM-YYYY')) <=$(=date(addmonths(today(),-13),'MMM-YYYY'))"}>} Data))

*IF(((sum( {$<CurYTDFlag = {'1'}>} Data)) -

sum({<MonthYear={">=$(=date(yearstart(today(),-1),'MMM-YYYY')) <=$(=date(addmonths(today(),-13),'MMM-YYYY'))"}>} Data))/

sum({<MonthYear={">=$(=date(yearstart(today(),-1),'MMM-YYYY')) <=$(=date(addmonths(today(),-13),'MMM-YYYY'))"}>} Data)

<0,1.2,-1.2)

And under max value:

=(((sum( {$<CurYTDFlag = {'1'}>} Data)) -

sum({<MonthYear={">=$(=date(monthstart(today(),-1),'MMM-YYYY')) <=$(=date(addmonths(today(),-13),'MMM-YYYY'))"}>} Data))/

sum({<MonthYear={">=$(=date(monthstart(today(),-1),'MMM-YYYY')) <=$(=date(addmonths(today(),-13),'MMM-YYYY'))"}>} Data))*1.1

The chart says no data can be displayed. Can anyone help me with this please?

Thanks so much

[jonathandienst · Accepted solution]

Blaze

I suggest the following approach:

• Try the guage with fixed min and max value to determine whether these are the problem. If the guage works, then the problem is with the limits.
• Verify that the two subexpressions that make up the expression return data. Create a straight table with no dimensions and the two subexpressions as the expression on the straight table.
• You can also test the min and max expressions in the straight table as well.

This should give an idea of where the problem is.

BTW - I am not sure about the date comparisons in MMM-YYYY format. You may want to change that to YYYY-MM format instead.

Jonathan

[#3]

Hi Jonathan

Thanks for the suggestions. did what you said and all of them return null except max value. that returns -1.1 so im going to review my formulas.

Thanks for the help