According to date wise creating the flags for 1 Month, 3 Month, 6 Months, like

[Ramu_i]

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

[Ramu_i]

Hi Vegar,

Problem is not resolved, I have share the Qvf file and share the problem.

Please help on that.

Thanks Advance.

Ram

[Vegar · Accepted solution]

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

[Aditya_Chitale · Accepted solution]

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 

[Ramu_i]

Hi Aditya Chitale,

I got solution thank for your are replay.

Regards,

Ram

[Ramu_i]

Hi Vegar,

Using above data calculate few columns,

1 Month, 3 months, YOY calculated ,

Need Few calculations MTD, QTD, YTD, Last 13 weeks, Last 40 weeks, Previous Month.

One filter is showing (Range Filter)- Month, Previous Month, 3 months, 6 months, 13 Months, MTD, QTD, MTD, Last 40 Weeks Like that.

One filter shows Date filter -Month, Week, Day.

Here different metrics are there KPI's Linke chart, Pie chart...

According to range selection it will reflect the data, and According to Date filter it will reflect the data.

Please help on that.

Thanks advance.

Ram

[Ramu_i]

Hi Aditya_Chitale,

Thanks for solution.

Thanks.

Ram