comparison of two months data using set analysis

[andrea0901]

Hi Team,

I have a requirement of comparing the last months data and current months data and updating the arrow upside and down. 

If the last month data is greater than current months data, the arrow should be down in the KPI chart. 

it has to compare for every month by taking the last month's data. 

I have a report date in my dashboard. Please assist me in doing this. 

Thank you in advance

[ggijben]

Hi @andrea0901 , 

Easiest way is to use a Master Calander so you can create flags.

If you want to do it in the front end, you can try:

Static:

Previous Month:

Sum({< Date={ ">=$(=AddMonths(MonthStart(Today()),-1))<= $(=AddMonths(MonthEnd(Today()),-1))" } >} Fieldname)

Current Month

Sum({< Date={ ">=$(=MonthStart(Today()))<= $(=MonthEnd(Today()))" } >} Fieldname)

Dynamic:

Previous month:

Sum({< Date={ ">=$(=AddMonths(MonthStart(Max(Date)),-1))<= $(=AddMonths(MonthEnd(Max(Date)),-1))" } >} Fieldname)

Current month:

Sum({< Date={ ">=$(=MonthStart(Max(Date)))<= $(=MonthEnd(Max(Date)))" } >} Fieldname)

* Change "Date"  to your date column name.

[Mark_Little]

Hi there,

This depends exactly what you are after, But if i understand correctly then the way i do this is with a mixture of scripting and front end.

Firstly i flag all the data that is in the Current month i.e.

IF(MONTH(TempDate)= MONTH(TODAY(1)) AND YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CURRENT_MONTH,

Then all the data in the prior month 

IF(MONTH(TempDate)= MONTH(ADDMONTHS(TODAY(1),-1)) AND YEAR(TempDate)=YEAR(ADDMONTHS(TODAY(1),-1)),1) AS F_PRIOR_MONTH,

Then I can use these in my set analysis for current and prior months.

Like SUM(<{F_CURRENT_MONTH={1}}>Sales)

You then create an if statement around the two for your up and down arrows.

[andrea0901]

Thank you . Till there it worked. Now i want to have the requirement as below

if (currentmonth) > (previousmonth) then the arrow should be up in the chart

if (currentmonth) < (previousmonth) then the arrow should be down in the chart

how can i given in one expression and show up and down. 

i am using kpi designer, where in one layer i have used up arrow icon and i need to provide the above statement in show/hide column. please advise on the same.