Help for Year vs Year and month year vs monthyear comarision

[Qlik1_User1]

$(=If(GetSelectedCount(reporting_year)=1,

    'reporting_year= ',

    'reporting_year=P([Test]::reporting_year)'

))

$(=If(GetSelectedCount(reporting_year)=1 AND GetSelectedCount(YearMonthfrom)=1 AND GetSelectedCount(YearMonthRollover)=0,

    ', effectivemonth_RY = {"$(vStart)"}',

''))

$(=If(GetSelectedCount(reporting_year)=1 AND 

      (

        (GetSelectedCount(YearMonthfrom)=0 AND GetSelectedCount(YearMonthRollover)=0) OR 

        (GetSelectedCount(YearMonthfrom)=1 AND GetSelectedCount(YearMonthRollover)=1)

      ),

    ', effectivemonth_RY = {">=$(vStart)<=$(vEnd)"}',

''))

$(=If(GetSelectedCount(reporting_year, 0, 'Test')=1,'reporting_year= ','reporting_year=P([Test]::reporting_year)'))
$(=If(GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=0,', effectivemonth_RY = {"$(=(vStart))"}',''))
$(=If((GetSelectedCount(YearMonthfrom)=0 and GetSelectedCount(YearMonthRollover)=0) OR (GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=1),', effectivemonth_RY = {">=$(vStart)<=$(vEnd)"}',''))

=Aggr(

    Count({<Year=, yemo={">=202211<=202303"}>} Country),

    Country

)

=IF(
reporting_year_temp = P(reporting_year)
AND Month_YearMonthfrom = P(effectivemonth_RY),
YearMonthfrom,
NULL()
)

=IF(

  Exists(reporting_year, reporting_year_temp)

  AND Exists(effectivemonth_RY, Month_YearMonthfrom),

  YearMonthfrom

)

=AGGR(

  IF(

    reporting_year_temp = {<reporting_year = P(reporting_year)>} reporting_year

    AND Month_YearMonthfrom = {<effectivemonth_RY = P(effectivemonth_RY)>} effectivemonth_RY,

    YearMonthfrom

  ),

  YearMonthfrom

)

=IF(

  reporting_year_temp = P({<Client>} reporting_year),

  reporting_year_temp

)

=IF(

  reporting_year_temp = P({<Client>} reporting_year)

  AND Month_YearMonthfrom = P({<Client>} effectivemonth_RY),

  YearMonthfrom

)

=IF(

  Match(reporting_year_temp, P(reporting_year)),

  reporting_year_temp

)

=IF(

  Match(reporting_year_temp, P(reporting_year)) AND

  Match(Month_YearMonthfrom, P(effectivemonth_RY)),

  YearMonthfrom

)

=AGGR(ONLY({1<reporting_year_temp=P(reporting_year), Month_YearMonthfrom=P(effectivemonth_RY)>}YearMonthfrom),YearMonthfrom)
=AGGR(ONLY({1<reporting_year_temp=P(reporting_year)>}reporting_year_temp),reporting_year_temp)

ROUND(Sum(
{<effectivemonth_RY={">=202603<=202603"}>}
(premium_without_commission * ExchangeRate)
),1/100)

=If(aggr(rank(TOTAL sum({<effectivemonth_RY={">=202603<="}>}(premium_without_commission*ExchangeRate))),location_name)<=10,location_name)

= 

$(=If(vIsRangeSelected,

If(Len(Trim(YearMonthRollover)) > 0,YearMonthfrom & ' - ' & YearMonthRollover,YearMonthfrom),

reporting_year

))

& ' (Premium excl comm): ' 

& Num($(Measure1),'#,##0K')

& Chr(10) &

$(=If(vIsRangeSelected,

If(Len(Trim(YearMonthRollover)) > 0,

Date(AddYears(Date#(YearMonthfrom,'MMM YYYY'),-1),'MMM YYYY') &' - '&Date(AddYears(Date#(YearMonthRollover,'MMM YYYY'),-1),'MMM YYYY'),

Date(AddYears(Date#(YearMonthfrom,'MMM YYYY'),-1),'MMM YYYY')),

MAX(reporting_year)-1

))

& ' (Premium excl comm): ' 

& Num($(Measure2),'#,##0K')

I have a bar chart
Selection vstart- 202606 and vend - 202702
For measure2
Vstartprev- 202506 and vend previous - 202602

For measure we have value for india but in bar chart no entry for india for measure2 why explain
Dimension=If(aggr(rank(TOTAL sum({<effectivemonth_RY={">=$(vStart)<=$(vEnd)"}>}(premium_without_commission*ExchangeRate))),location_name)<=10,location_name)

Measure 1=
ROUND(Sum(

{<reporting_year=
// effectivemonth_RY={">=$(vStart)<=$(vEnd)"}
$(=If(GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=0,', effectivemonth_RY = {"$(=(vStart))"}',''))
$(=If((GetSelectedCount(YearMonthfrom)=0 and GetSelectedCount(YearMonthRollover)=0) OR (GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=1),', effectivemonth_RY = {">=$(vStart)<=$(vEnd)"}',''))

}

(premium_without_commission * ExchangeRate)

),1/100)

Measure 1 Label  =If(vIsRangeSelected,
If(Len(Trim(YearMonthRollover)) > 0,YearMonthfrom & ' - ' & YearMonthRollover,YearMonthfrom),
reporting_year
)

Measure2=
ROUND(Sum(

{<reporting_year=
// effectivemonth_RY={">=$(vStart)<=$(vEnd)"}
$(=If(GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=0,', effectivemonth_RY = {"$(=(vStartPrev))"}',''))
$(=If((GetSelectedCount(YearMonthfrom)=0 and GetSelectedCount(YearMonthRollover)=0) OR (GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=1),', effectivemonth_RY = {">=$(vStartPrev)<=$(vEndPrev)"}',''))

}

(premium_without_commission * ExchangeRate)

),1/100)

Measure 2 Label =If(vIsRangeSelected,
If(Len(Trim(YearMonthRollover)) > 0,
Date(AddYears(Date#(YearMonthfrom,'MMM YYYY'),-1),'MMM YYYY') &' - '&Date(AddYears(Date#(YearMonthRollover,'MMM YYYY'),-1),'MMM YYYY'),
Date(AddYears(Date#(YearMonthfrom,'MMM YYYY'),-1),'MMM YYYY')),
MAX(reporting_year)-1
)

=If(aggr(rank(TOTAL sum({<effectivemonth_RY={">=$(vStart)<=$(vEnd)"}>}(premium_without_commission*ExchangeRate))),location_name)<=10,location_name)

ROUND(Sum(

{<reporting_year=
// effectivemonth_RY={">=$(vStart)<=$(vEnd)"}
$(=If(GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=0,', effectivemonth_RY = {"$(=(vStart))"}',''))
$(=If((GetSelectedCount(YearMonthfrom)=0 and GetSelectedCount(YearMonthRollover)=0) OR (GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=1),', effectivemonth_RY = {">=$(vStart)<=$(vEnd)"}',''))
>}

(premium_without_commission * ExchangeRate)

),1/100)

ROUND(Sum(

{<reporting_year=
// effectivemonth_RY={">=$(vStart)<=$(vEnd)"}
$(=If(GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=0,', effectivemonth_RY = {"$(=(vStartPrev))"}',''))
$(=If((GetSelectedCount(YearMonthfrom)=0 and GetSelectedCount(YearMonthRollover)=0) OR (GetSelectedCount(YearMonthfrom)=1 and GetSelectedCount(YearMonthRollover)=1),', effectivemonth_RY = {">=$(vStartPrev)<=$(vEndPrev)"}',''))
>}

(premium_without_commission * ExchangeRate)

),1/100)

PolicyMonths:

LOAD DISTINCT

effectivemonth_RY as MonthKey

Resident Fact_PolicyDates;

LEFT JOIN (FinalCalendar)

LOAD

MonthKey as Month_YearMonthfrom,

1 as HasPolicy

Resident PolicyMonths;

YearMonthfrom:

AGGR(

ONLY({<

reporting_year_temp = P(reporting_year),

HasPolicy = {1}

>} YearMonthfrom),

YearMonthfrom

)

YearMonthRollover:

AGGR(

ONLY({<

HasPolicy = {1},

YearMonthfrom = P(YearMonthfrom)

>} YearMonthRollover),

YearMonthRollover

)

HI
have created the rolling 12 month year using below code
Now i have created the filters YearMonthfrom and YearMonthRollover

based on reporting year selection values in YearMonthfrom and YearMonthRollover are coming.
But I want to show only months for which the client has policy period start dates within the selected Reporting Year will be shown. Months without any policy period start dates for that year will be omitted.

YearMonthfrom =AGGR(ONLY({1<reporting_year_temp=P(reporting_year)>}YearMonthfrom),YearMonthfrom)
YearMonthRollover =Aggr(
Only({1<
YearMonthfrom = {"=GetSelectedCount(YearMonthfrom)=1"} * P(YearMonthfrom)
>} YearMonthRollover),
YearMonthRollover
)

Fact_PolicyDates:

LOAD `policy_id` & reporting_year as Fact_PolicyDateskey,
Date#(effectivedate1, 'M/DD/YYYY') as effectivedate,
Date(MonthStart(Date#(effectivedate1, 'M/DD/YYYY')), 'MMM YYYY') as effectivedate_Mm_YY,
Num(Month(Date#(effectivedate1,'M/DD/YYYY'))) as effectivedate_Mm,
reporting_year&Num(Month(Date#(effectivedate1,'M/DD/YYYY')),'00') as effectivemonth_RY

FROM [$(v_QVDPath)/vw_policy.qvd] (qvd)
where MATCH(policystate,'before_first_renewal','first_renewal','subsequent_renewal','after_renewal','historical_period','copy_of_renewal_after_peer_review_renewed_policy','copy_of_annual_review_after_peer_review_renewed_policy');

//ADO - 2306555, This is an Island table.
MonthDim:
LOAD * INLINE [
MonthID, MonthName
1, January
2, February
3, March
4, April
5, May
6, June
7, July
8, August
9, September
10, October
11, November
12, December
];

Temp_Year: //Temp table - to get Min, Max Month and Year
Load
distinct
MIN(reporting_year) as minyear,
MAX(reporting_year) as maxyear
FROM [lib://GBMA - Data Model/Data/vw_policy.qvd]
(qvd) Where len(reporting_year)=4 ; // to avoid currupted data like 43127 in RY fields

Let varminyear = Num(Peek('minyear', 0, 'Temp_Year'));
Let varmaxyear = Num(Peek('maxyear', 0, 'Temp_Year'));

Temp_Year2: //Temp Master Year table
LOAD
NUM($(varminyear) + Iterno()-1) As "year"
AutoGenerate 1 While $(varminyear) + IterNo() -1 <= $(varmaxyear);

Temp_MonthYear:

load "MonthName"
Resident MonthDim;

JOIN(Temp_MonthYear)

load "year"
Resident Temp_Year2;

DROP Tables MonthDim,Temp_Year,Temp_Year2;
NoConcatenate

FiscalYearmonthto:

Load Distinct "MonthName"&' '&"year" as fiscalyearmonthfrom, "year" as reporting_year_temp
Resident Temp_MonthYear;
DROP Table Temp_MonthYear;

NoConcatenate

TempCalendar:
LOAD
reporting_year_temp,
fiscalyearmonthfrom,
Date#(fiscalyearmonthfrom, 'MMM YYYY') as YearMonthDate
Resident FiscalYearmonthto;
NoConcatenate

FinalCalendar:
LOAD
reporting_year_temp,
Date(MonthStart(YearMonthDate),'MMM YYYY') as YearMonthfrom,
year(Date#(YearMonthDate,'MMM YYYY')) &Num(Month(Date#(YearMonthDate,'MMM YYYY')),'00') as Month_YearMonthfrom,
Date(AddMonths(YearMonthDate, IterNo()-1),'MMM YYYY') as YearMonthRollover,
YEAR(Date#(Date(AddMonths(YearMonthDate, IterNo()-1),'MMM YYYY'),'MMM YYYY'))&Num(Month(Date#(Date(AddMonths(YearMonthDate, IterNo()-1),'MMM YYYY'),'MMM YYYY')),'00') as Month_YearMonthRollover

Resident TempCalendar
While IterNo() <= 12;

DROP Tables TempCalendar, FiscalYearmonthto;

Kindly suggest on below how to handle this scenario.

I have a comparison table chart which show selected year vs selected year-1 data.

Dim - Country
Measure 1- ROUND(Sum({<reporting_year={"$(=max(reporting_year))"}>} premium_without_commission),1/100)
Measure 2- ROUND(Sum({<reporting_year={"$(=max(reporting_year)-1)"}>} premium_without_commission),1/100)

Now 3 new fields are added startdate, end date, renewdate

When a user selects Reporting Year = 2026 and Start Date = Jan 2026, Feb 2026, end date= Nov 2026, Dec 2026 in the filters, they expect to see the premium for start date= Jan 2026, Feb 2026 and end date= Nov 2026, Dec 2026 in measure1 and in measure2 premium for start date= Jan 2025, Feb 2025 and end date= Nov 2025, Dec 2025

If nothing is select in any of startdate, end date, renewdate fields just show the premium for selected year in measure 1 and selected year -1 in measure2

I have created a dashsabord where having multiple charts like pie, bar, table some chart are like simply show data based on selection of reporting year and some chart where we can comparing data based on selection of reporting year and selected reporting year-1.

Now 2 new filters are added YearMonthfrom and YearMonthRollover.

Case1
if user selected just reporting year - show the data based on reporting year
NON YOY chart - show data bsed on selected reporting year like SUM(premium)
YOY charts - show data based on selected reporting year and selected reporting year-1 like SUM(premium)

Case 2
if user selected just values in YearMonthfrom and YearMonthRollover
NON YOY chart - show data bsed on selected range like SUM(premium) and ignore reprting year selection
YOY charts - show data based on selected range and selected range-1 like SUM(premium) and ignore reprting year selection

// Flag to check if YearMonth range is selected

vIsRangeSelected = If(Len(YearMonthFrom)>0, 1, 0)

// Start of current period

vStart = If(vIsRangeSelected, YearMonthFrom, ReportingYear & '01')

// End of current period

vEnd = If(vIsRangeSelected, YearMonthRollover, ReportingYear & '12')

// Start of previous period (1 year back)

vStartPrev = Date(AddYears(Date#(vStart,'YYYYMM'),-1),'YYYYMM')

// End of previous period (1 year back)

vEndPrev = Date(AddYears(Date#(vEnd,'YYYYMM'),-1),'YYYYMM')

SUM(
{<YearMonth = {">=$(vStart)<=$(vEnd)"}>} Premium
)

SUM({<YearMonth={">=$(vStart)<=$(vEnd)"}>} Premium)

-

SUM({<YearMonth={">=$(vStartPrev)<=$(vEndPrev)"}>} Premium)

vCurrentLabel = If(vIsRangeSelected, 'Selected Range', 'Reporting Year ' & ReportingYear)

vPreviousLabel = If(vIsRangeSelected, 'Previous Year Range', 'Previous Year ' & (ReportingYear-1))

[robert_mika]

Try like that:

M1

ROUND(Sum({<reporting_year = {"$(=Max(reporting_year))"}
>} premium_without_commission),0.01)

M2

ROUND( Sum({< reporting_year = {"$(=Max(reporting_year)-1)"},
startdate = {"$(=Concat(DISTINCT AddYears(startdate, -1), Chr(39) & ',' & Chr(39)))"},
enddate = {"$(=Concat(DISTINCT AddYears(enddate, -1), Chr(39) & ',' & Chr(39)))"},
renewdate = {"$(=Concat(DISTINCT AddYears(renewdate, -1), Chr(39) & ',' & Chr(39)))"}
>} premium_without_commission), 0.01)