topic Re: Calculate Previous Month Cost based on filter selection in App Development

Calculate Previous Month Cost based on filter selection

satya_s — Tue, 09 Jan 2024 15:42:02 GMT

Hi,

I am trying to show the month (selected) and previous month cost in a report but for some reason the previous month cost is showing the same value as current month. I need some help identify the issue.

Step 1: Loading the data

[DealsData]:
LOAD
Date(Floor(TRAFFIC_DATE),'YYYY-MM-DD') AS TRAFFIC_DATE,
"YEAR",
"MONTH",
CARRIER_SHORT_NAME,
SUM(COST) AS COST
FROM [lib://Du_QS_QVD_Folder_Conn/DealsData.qvd] (qvd)
GROUP BY Date(Floor(TRAFFIC_DATE),'YYYY-MM-DD'),
"YEAR",
"MONTH",
CARRIER_SHORT_NAME;

Step 2:

Created a calculated column in the sheet i.e.

PREVIOUS_MONTH_COST = Sum({$<YEAR,MONTH, TRAFFIC_DATE={"=$(=Date(addmonths(Max(TRAFFIC_DATE),-1),'YYYY-MM'))"}>}COST)

Step 3: On selecting the Month and Year, the Previous Month Cost shows the same value as Current Month Cost

I tried the following formula too but that didn't work either

Sum({<TRAFFIC_DATE={">=$(=Date(MonthStart(addmonths(Max(TRAFFIC_DATE),-1)),'YYYY-MM-DD')) <=$(=Date(addmonths(Max(TRAFFIC_DATE),-1),'YYYY-MM-DD'))"},YEAR,MONTH>}COST)

Can you please help rectify the expression above?

Re: Calculate Previous Month Cost based on filter selection

MatheusC — Tue, 09 Jan 2024 18:18:58 GMT

@satya_s

see with the formula below and ignoring month= filter

sum({<TRAFFIC_DATE={">=$(=Date(MonthStart(Date(Max(TRAFFIC_DATE)),-1),'YYYY-MM-DD')) <=$(=Date(MonthEnd(Date(Max (TRAFFIC_DATE)),-1),'YYYY-MM-DD'))"},MONTH=>}COST)

Att, Matheus

Re: Calculate Previous Month Cost based on filter selection

satya_s — Tue, 09 Jan 2024 19:22:00 GMT

Thank you @MatheusC !! The solution worked and the key to this is to ignore the month filter in the dashboard report which you mentioned. That really helped otherwise it was showing 2 entries (one for current month and another for the previous month).