topic Re: comparison of two months data using set analysis in Visualization and Usability

comparison of two months data using set analysis

andrea0901 — Tue, 12 Jul 2022 11:52:20 GMT

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

Re: comparison of two months data using set analysis

ggijben — Tue, 12 Jul 2022 12:14:53 GMT

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.

Re: comparison of two months data using set analysis

Mark_Little — Tue, 12 Jul 2022 12:11:44 GMT

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.

Re: comparison of two months data using set analysis

andrea0901 — Tue, 12 Jul 2022 13:39:44 GMT

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.