Calculating Measures as per Monthly/Quaterly/Yearly selection made on Filter Pane.

Binay_Sharma — Thu, 08 Apr 2021 12:53:39 GMT

Hello, First of all I am a novice, but I have decided to learn Qlik,

For many of you, this sounds simple question, but I have spent enough time on this and I feel that it is time to seek for help.

I am trying to use the filter pane so that
Gross fees is calculated as per date/month/quarter/year -- whatever range user wants to select.

For example if I select Year 2021 and Quarter as Q1 I should get the sum of gross for all the months that falls on Quarter 1 of year 2021.

Currently, the way the app is behaving is, the sum is coming as total cumulative sum (here the sum of #uvGrossFee), irrespective of the year or quarter I select.

If I select Year 2019 - I am getting gross as 38.83 M

If I select Year 2020- I am getting gross as 88 M

and If I select 2021 - I am getting 97.36 Million.

Looks like value is getting cumulative - 96M. For 2021, there are only 2 months of data and the value should come around 8 M.

Can someone point me what adjustment I should make on my set analysis?

I tried to calculate the Gross fees as below. Tried some other approaches as well.

sum({<[_Acct_Appl_Code_Flag] ={1},[Cycle End Date]={'>=$(=MonthStart(Min([Cycle End Date])))'} >} [#uvGross Fee])

or
sum({<_CMTD_Flag ={1},[_Acct_Appl_Code_Flag] ={1} >} [#uvGross Fee])

For reference, I have created a Master Calendar table as below and I am fetching Month, Quarter, year from this table.

Temp_Dates:
LOAD
FieldValue('Cycle End Date', RecNo()) as "Cycle End Date"
AutoGenerate
FieldValueCount('Cycle End Date')
;

Join (Temp_Dates)
load

Date(floor(MonthEnd(AddMonths(MinDate, IterNo() -1)))) as [As Of Date]
WHILE
Date(floor(MonthEnd(AddMonths(MinDate, IterNo() -1)))) <= Date(floor(MonthEnd(MaxDate)))
;

load
Date(floor(MonthEnd(Min("Cycle End Date")))) as MinDate,
Date(floor(MonthEnd(Max("Cycle End Date")))) as MaxDate
resident
Temp_Dates
;

AsOfDate:
load
"Cycle End Date",
[As Of Date] ,
Year([As Of Date] ) AS Year,
Month([As Of Date] ) AS Month,
'Q' & ceil(month([As Of Date] ) / 3) AS Quarter,
Year([As Of Date] ) &'-'& 'Q' & ceil(month([As Of Date] ) / 3)
AS QuarterYear,

Date([As Of Date] , 'MMM-YYYY') AS MonthYear,

InMonth("Cycle End Date", [As Of Date] , 0) * -1 AS _CMTD_Flag,
InMonth("Cycle End Date", [As Of Date] , -12) * -1 AS _PMTD_Flag,
InMonth("Cycle End Date", [As Of Date] , -1) * -1 AS _PM_Flag,

InYearToDate("Cycle End Date", [As Of Date] , 0) * -1 AS _CYTD_Flag,
InYearToDate("Cycle End Date", [As Of Date] , -1) * -1 AS _PYTD_Flag,
(AddMonths("Cycle End Date", 12) > [As Of Date] ) * -1 AS _R12_Flag,
(AddMonths("Cycle End Date", 12) <= [As Of Date] ) *
(AddMonths("Cycle End Date", 24) > [As Of Date] ) AS _PR12_Flag,

(AddMonths("Cycle End Date", 6) > [As Of Date] ) * -1 AS _R6_Flag,

(AddMonths("Cycle End Date", 6) <= [As Of Date] ) *
(AddMonths("Cycle End Date", 12) > [As Of Date] ) AS _PR6_Flag,

(AddMonths("Cycle End Date", 12) <= [As Of Date] ) *
(AddMonths("Cycle End Date", 18) > [As Of Date] ) AS _PYR6_Flag,

(AddMonths("Cycle End Date", 3) > [As Of Date] ) * -1 AS _R3_Flag,

(AddMonths("Cycle End Date", 3) <= [As Of Date] ) *
(AddMonths("Cycle End Date", 6) > [As Of Date] ) AS _PR3_Flag,

(AddMonths("Cycle End Date", 12) <= [As Of Date] ) *
(AddMonths("Cycle End Date", 15) > [As Of Date] ) AS _PYR3_Flag

resident
Temp_Dates
where
"Cycle End Date"<=[As Of Date]
;

drop table Temp_Dates;

Re: Calculating Measures as per Monthly/Quaterly/Yearly selection made on Filter Pane.

Dalton_Ruer — Thu, 08 Apr 2021 16:14:56 GMT

Your Year Month field seems to be messed up. Although you filtered to 2021 it is showing them all.

Re: Calculating Measures as per Monthly/Quaterly/Yearly selection made on Filter Pane.

Binay_Sharma — Thu, 08 Apr 2021 18:55:26 GMT

Hi Dalton, That is why I was seeking help.

From further research, what I have found so far is, As of Date solution requires that a single As of Date should be selected at all times. I am still trying to figure out the solution for this.

topic Re: Calculating Measures as per Monthly/Quaterly/Yearly selection made on Filter Pane. in App Development

Calculating Measures as per Monthly/Quaterly/Yearly selection made on Filter Pane.

Re: Calculating Measures as per Monthly/Quaterly/Yearly selection made on Filter Pane.

Re: Calculating Measures as per Monthly/Quaterly/Yearly selection made on Filter Pane.