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

[Aditya_Chitale]

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

[Ramu_i]

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

[Aditya_Chitale]

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

[Ramu_i]

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

[Aditya_Chitale]

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

[Ramu_i]

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

[Aditya_Chitale]

If possible ,can you share sample qvf ?

Regards,
Aditya

[Ramu_i]

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

[Vegar]

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.