# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
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)

• ### Visualization

2 Solutions

Accepted Solutions
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))

Partner - Specialist

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,1002021,1,2,1202021,1,3,1302021,2,4,2002021,2,5,2332021,2,6,2452021,3,7,2642021,3,8,2552021,3,9,3412021,4,10,5562021,4,11,7832021,4,12,1672022,1,1,6872022,1,2,476];NoConcatenatetable2:load Year,Quarter,Month,Value,if(Quarter<>Previous(Quarter),1) as flag_month__quarterRESIDENT table1order by Year,Quarter, Month desc;drop table table1;`

and then use the flag in your measure

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

Hope this helps.

Best,

3 Replies
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))

Partner - Specialist

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,1002021,1,2,1202021,1,3,1302021,2,4,2002021,2,5,2332021,2,6,2452021,3,7,2642021,3,8,2552021,3,9,3412021,4,10,5562021,4,11,7832021,4,12,1672022,1,1,6872022,1,2,476];NoConcatenatetable2:load Year,Quarter,Month,Value,if(Quarter<>Previous(Quarter),1) as flag_month__quarterRESIDENT table1order by Year,Quarter, Month desc;drop table table1;`

and then use the flag in your measure

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

Hope this helps.

Best,

Creator II
Author

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

Tags
Community Browser