Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
PradeepK
Creator II
Creator II

Latest Possible Month data for given Quarter and Year

Build Straight table, which always gives latest month data for provided Year-Quarter-Month / Year-Quarter /Year dimension 

No Selections or filters applied

Below is an example of how it should look like -

Data Sample / Monthly value

Year Quarter Month Value 
2021 1 1 100
2021 1 2 120
2021 1 3 130
2021 2 4 200
2021 2 5 233
2021 2 6 245
2021 3 7 264
2021 3 8 255
2021 3 9 341
2021 4 10 556
2021 4 11 783
2021 4 12 167
2022 1 1 687
2022 1 2 476

 

Quarterly Max month data

Year Quarter Value
2021 1 130
2021 2 245
2021 3 341
2021 4 167
2022 1 476

 

Yearly Max month data

Year Value
2021 167
2022 476

 

Labels (7)
2 Solutions

Accepted Solutions
justISO
Specialist
Specialist

Hi, for Quarterly Max you can try:

sum(IF(Month = Aggr(NODISTINCT Max({1}Month),Quarter,Year), Value))

for Yearly Max approach the same

sum(IF(Month = Aggr(NODISTINCT Max({1}Month),Year), Value))

View solution in original post

RafaelBarrios
Partner - Specialist
Partner - Specialist

Hi @PradeepK 

as i always try not to use aggr, mainly in large applications, i would suggest to solve it in the script.

what would I do is ordering by year, quarter and month in descending order and read comparing with previuos record to mark the maximum month for each quarter

table1:
load * inline [
Year,Quarter,Month,Value
2021,1,1,100
2021,1,2,120
2021,1,3,130
2021,2,4,200
2021,2,5,233
2021,2,6,245
2021,3,7,264
2021,3,8,255
2021,3,9,341
2021,4,10,556
2021,4,11,783
2021,4,12,167
2022,1,1,687
2022,1,2,476

];

NoConcatenate
table2:
load
Year,
Quarter,
Month,
Value,
if(Quarter<>Previous(Quarter),1) as flag_month__quarter
RESIDENT table1
order by Year,Quarter, Month desc;

drop table table1;

RafaelBarrios_0-1666204042200.png

 

and then use the flag in your measure

sum({< flag_month__quarter={1} >}Value)

RafaelBarrios_1-1666204102096.png

Hope this helps.

Best,

View solution in original post

3 Replies
justISO
Specialist
Specialist

Hi, for Quarterly Max you can try:

sum(IF(Month = Aggr(NODISTINCT Max({1}Month),Quarter,Year), Value))

for Yearly Max approach the same

sum(IF(Month = Aggr(NODISTINCT Max({1}Month),Year), Value))

RafaelBarrios
Partner - Specialist
Partner - Specialist

Hi @PradeepK 

as i always try not to use aggr, mainly in large applications, i would suggest to solve it in the script.

what would I do is ordering by year, quarter and month in descending order and read comparing with previuos record to mark the maximum month for each quarter

table1:
load * inline [
Year,Quarter,Month,Value
2021,1,1,100
2021,1,2,120
2021,1,3,130
2021,2,4,200
2021,2,5,233
2021,2,6,245
2021,3,7,264
2021,3,8,255
2021,3,9,341
2021,4,10,556
2021,4,11,783
2021,4,12,167
2022,1,1,687
2022,1,2,476

];

NoConcatenate
table2:
load
Year,
Quarter,
Month,
Value,
if(Quarter<>Previous(Quarter),1) as flag_month__quarter
RESIDENT table1
order by Year,Quarter, Month desc;

drop table table1;

RafaelBarrios_0-1666204042200.png

 

and then use the flag in your measure

sum({< flag_month__quarter={1} >}Value)

RafaelBarrios_1-1666204102096.png

Hope this helps.

Best,

PradeepK
Creator II
Creator II
Author

Thanks @RafaelBarrios. I used the similar approach. Ended up creating Max_Month_Flag for every quarter in Calendar table.