I want to calculate total followers from starting day of every month to current date (for example: if current date is 16th august 2023 and i want to see the followers month wise from starting day of the month, to 16th of every month and this 16th should be updated dynamically by setting as today’s date )
Basically if i choose months granularity, then data should be displayed from starting day of the month till 16th of that month itself , for all months separately
currently i have created a parameter
data is displaying correctly for the formula i have used for MOM/QOQ/YOY
as u can see when i select MOM/QOQ/YOY then in the above formula upto:
“ifelse(${MOM}=‘MOM/QOQ/YOY’,periodOverPeriodPercentDifference(sum(followers), date, MONTH, 1),”
here its working fine as per the required dropdown (MOM/QOQ/YOY)
But when i select MTD/YTD/QTD it should display data from beginning of every month to current date only, (since i was unable to get mtd formula right i added another function to show that i want it to work if the first condition is not executed in the ifelse),
i.e if Mom/QoQ/YoY is not selected the the other funtion selected i.e , should be displaying MTD/QTD/YTD)
the other function is defined as:
for example if today’s date is 21st then regardless of month and year data should be displaying for 1st of every month to 21st of that month itself
MTD/QTD/YTD
1 jan 2023 to 21 jan 2023
1 feb 2023 to 21 feb 2023
1 march 2023 to 21 march 2023
so need this function in the else condition when I select MTD/QTD/YTD
Could you try using the periodtodate functions for this?
Using your above style, you could do something like this: ifelse(${MTD}=‘MTD/QTD/YTD’, periodtoDatesSum(abs{followers}, {date}, MONTH, now()), count(followers)
You can technically omit the now() function and it will default to today’s date
I tried using the below formula as u had suggested
Formula: ifelse(${MOM}=‘MTD/QTD/YTD’, periodToDateSum((abs(followers)), date, MONTH), count(followers))
data is displayed from 1st of that month to 23rd of this year and this month
even after omitting now() which you suggested i’m not able to get the desired output
I have attached the final / required output in the last 2 images)
Actually i don’t want followers from start of every month to this years current date, I want followers from start of every month to the day of today’s date in their particular months itself. below is the clear picture of the requirement
As you can see here when MTD button is selected then data is displaying for each month from 1st to 23rd of that month and year itself (since today is 23)
When its split to day wise, we can see that calculations are getting performed till 23 of that month and year itself
But in QuickSight when its split to day wise after applying the formula we can still see that its getting calculated after 23 of the month
Hello @Akshaymanjunath , something you could try would be to add a calculated field to create a new column for the day value of the date using something like truncDate({Date}, “DD”). You can add it to the table and hide it from displaying.
Then create a parameter that would set to the current day in the same format. Once completed, filter the table by the calculated function for the day to be less than or equal to the value in the parameter.
We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question
