How to calculate month over month comparison when the date filter is applied on a visual eg: table

Question & Answer
,
1

I am trying to create a calculated field which shows previous months value according to the date range selected in a filter.
For eg: If user selects start date as March 1st and End Date as March 13th, the calculated field will show the value for whole Feb month.

I have created 3-4 different fields but realised that when the date filter is applied. The dataset gets filtered and I don’t have the data of last month so the calculation always gives null.

I am not sure how to resolve this issue.

Any help is appreciated.

Below is a sample calculation which works without filter but in real use case the filter will be applied on table and we will be showing the comparison for previous period.

countIf({cs_transaction_id}, dateDiff({cs_create_time},endDate,‘MM’)= 1 AND dateDiff({cs_create_time},endDate,‘YYYY’)= 0 AND {cs_response_code}=‘0’)/countIf({cs_transaction_id}, dateDiff({cs_create_time},endDate,‘MM’)=1 AND dateDiff({cs_create_time},endDate,‘YYYY’)= 0)

cs_create_time is a datetime column. endDate is the end date of the date range used in filter.

2

hi @Sunny1208,

I think there is a dashboard in DemoCentral showcasing how to do it.

would you be able to see if this resolves your problem?

kind regards,
Wakana

3

Thanks for the response, I will go through the demo dashboard.
One more thing I want to ask is about the column names for table, can we have a dynamic labelling?
For more explanation and example, I have a table which has columns
Users, Approval rate(The period by filter is applied on), Current Month(current month approval rate), Previous Month(-1 month approval rate), Prior Month(-2 month approval rate)

Now, lets say filter is march 1 to march 15. Can I have dynamic column labels which will change as
Users, Approval rate, March Approval rate, Feb Approval rate, Jan Approval rate

So, the current month dynamically changed to current ongoing month,
-1 approval rate changed to Feb as it was previous month and
-2 approval rate changed to Jan as it was prior month

Let me know if this is understandable or not!

4

Hi @Sunny1208,
In reference to dynamic column labeling; the only option you have to set dynamically would be through the use of a parameter value. You are able to assign a parameter value to your column name:

image
image217×120 4.44 KB

But currently, you are unable to setup a dynamic label in the way you would like to.

Let us know if you have any additional questions, if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

5

Hi @Sunny1208,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!