Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have date filed as DD/MM/YYYY HH:MM:SS format.
In script side will derive the 1 month, 3 month, 6 months , YOY flags.
1 Month:
----------------
If current date is 25/11/2022 - 1 month is --- 25/11/2022 to 24/10/2022
If current date is 26/11/2022 - 1 month is --- 26/11/2022 to 25/10/2022 Like 1 Month
3 Months:
------------------------
If current date is 25/11/2022 - 3 month is --- 25/11/2022 to 24/9/2022
If current date is 26/11/2022 - 3 month is --- 26/11/2022 to 25/9/2022 Like 3 Month
6 Month:
---------------------
If current date is 25/11/2022 - 6 month is --- 25/11/2022 to 24/5/2022
If current date is 26/11/2022 - 6 month is --- 26/11/2022 to 25/5/2022 Like 6 Month
YOY:
----------------------
is Jan to Till date and Previous year of Jan to Current date of Previous date.
Please help on this .
Thanks advance.
Ram
If you want the periods to be static based on a fixed date the you can do like this:
Salesshipments:
LOAD
Division,
Date_Value,
Date_Value & ' to ' & Addmonths(Date_Value-1,-1) as [1 Month],
Date_Value & ' to ' & Addmonths(Date_Value-1,-3) as [3 Month],
Date_Value & ' to ' & Addmonths(Date_Value-1,-6) as [6 Month],
yearstart(Date_Value,0,1) & ' to ' & Date_Value as [YOY CY],
yearstart(Date_Value,-1,1) & ' to ' & Addmonths(Date_Value,-12) as [YOY PY],
"Business Unit Name",
Segment,
"Source Syatem",
Amount,
Region,
"Product ID",
Customer,
"Shipment ID",
"Order ID",
"Shipment Type",
Travel_Type,
Travel_ID,
"Delivery Date",
Shipments
FROM [lib://DataFiles/Financial Project.xlsx]
(ooxml, embedded labels, table is Salesshipments);
let vToday = today() ;
//Loop that handles 1,3,6 and 13 month DateRange values.
for each _interval in 1, 3, 6, 13
DateRanges:
LOAD
dayname('$(vToday)'-IterNo()+1) as Date_Value,
'$(_interval) Month' as DateRange
AutoGenerate 1
While
addmonths('$(vToday)',-$(_interval))<'$(vToday)'-IterNo()+1
;
next _interval
//YOY CY
Concatenate LOAD
dayname('$(vToday)'-IterNo()+1) as Date_Value,
'YOY CY' as DateRange
AutoGenerate 1
While
addmonths('$(vToday)',-12)<'$(vToday)'-IterNo()+1
;
//YOY LY
Concatenate LOAD
dayname(addmonths('$(vToday)',-12)-IterNo()+1) as Date_Value,
'YOY LY' as DateRange
AutoGenerate 1
While
addmonths('$(vToday)',-24)<addmonths('$(vToday)',-12)-IterNo()+1
;
exit script
Hi Ramu,
I saw the qvf that you provided. In that you have added single sheet with sum(Amount) as measure. now when you are talking about selecting 1 month from filter, what date range you intend to consider for calculating sum of amount. for eg. in you data you have latest date as 26 nov 2022. so do you want to add 1 month, 3 month, 6 month,etc filter on this date ?
That's what I meant by start date in my previous reply.
Regards,
Aditya
Use this in your script:
test:
load
date,
date & ' to ' & Addmonths(date-1,-1) as [1 Month],
date & ' to ' & Addmonths(date-1,-3) as [3 Month],
date & ' to ' & Addmonths(date-1,-6) as [6 Month],
yearstart(date,0,1) & ' to ' & date as [YOY CY],
yearstart(date,-1,1) & ' to ' & Addmonths(date,-12) as [YOY PY]
from [lib://xyz.xlsx];
Regards,
Aditya
Hi Aditya Chitale,
Thanks for solution. It's working fine.
I have sales data, According to that display the data.
if select the 1 month , KPI and all charts display the 1 Month data,
If select the 3 Month , reflect the 3 months data
If YOY it will display the YOYCY and YOYPY display the chart.
Thanks advance.
Ram
you can use getfieldselections() function in an if() statement.
Somewhat like this:
if ( getfieldselections(1 Month), sum({<Date={'>=$(startdate)<=$(1 Month EndDate)}'>}Sales),
if ( getfieldselections(3 Month), sum({<Date={'>=$(startdate)<=$(3 Month EndDate)}'>}Sales),
if ( getfieldselections(6 Month), sum({<Date={'>=$(startdate)<=$(6 Month EndDate)}'>}Sales),
if ( getfieldselections(YOY CY), sum({<Date={'>=$(startdate)<=$(YOY CY MonthEndDate)}'>}Sales),
if ( getfieldselections(YOY PY), sum({<Date={'>=$(PY startdate)<=$(YOY PY MonthEndDate)}'>}Sales)
)))))
If you are using same measure expression in all charts, I recommend you to create master measure to avoid rewriting expression separately in each chart.
Regards,
Aditya
Hi Aditya Chitale,
Thanks for solution,
Here if ( getfieldselections(1 Month), sum({<Date={'>=$(startdate)<=$(1 Month EndDate)}'>}Sales),
Startdate means , how to find the start date and end date in that.
Can you please more info on that.
Thanks advance.
Ram
Start Date will be the max date on basis of which you want to shift 1 month,3 months,6 months ,etc backwards.
If you are considering the max date as start date, you can just use max(Date) function inside set analysis in place of start date and calculate 1 month,3 month, etc on basis of that.
Regards,
Aditya
Hi Aditya Chitala,
I tried but not working
I have implemented :
if ( getfieldselections(1 Month), sum({<Date_Value={'>=$(=Max(Date_Value))<=$(1 Month EndDate)}'>}Sales)
Please correct the formula.
Thanks advance.
Ram
If possible ,can you share sample qvf ?
Regards,
Aditya
Hi Aditya Chitala,
I have shared QVF file.
In filter I have values like 1 month, 3 months, 6 months, YOY thing. (This filter point i have created one inline table but these value are calculated in main table and range field is there in main table).
If select the 1 Month it will display the one month data other filters also there, in that also show the respected values.
Thanks advance.
Ram
Sounds like your problem is solved. That's great.
Please mark the response as an accepted solution. It will help others with similar questions and tell the community that your issue is solved and don't need more attention.