Hiding quarter if data is incomplete

[jorn188]

Hi,

I  have the following data:

2020 (jan,feb, march....  untill november, december)

results in: 2020-q1,2020-q2,2020-q3,2020-q4

2021 (jan, feb, march.... until  July, August)

results in: 2021-q1,2021-q2,2021-q3,

I want to only have quarters shown for 'complete' quarters. Example: Q3 2021 is not complete since only July and August are available in 2021.  In 2020 q3 is complete since there is data for July, August and September.

I'm using following expression in load script:  Year (TempDate)&'-Q' & Ceil(Month (TempDate)/3) AS YearQuarter,

This results in q3 2021 being created when only July and August are available which shouldnt happen since data for september is not available. q4 2021 is not generated since no data exists (as it should).

How can I update this so it excludes incomplete quarters?

Thanks!

[Kushal_Chawda · Accepted solution]

@jorn188  probably you can create a flag in script. You already have YearQuarter in your calendar, if you also have MonthYear then you can do something like below

left join(Calendar)
LOAD YearQuarter,
     if(Cnt_Month=3,1,0) as is_complete_quarter;
LOAD YearQuarter,
     count(distinct MonthYear) as Cnt_Month
resident Calendar
group by YearQuarter;

Now you can use the flag in your measure. for eg if your measure is sum(Sales), you could write it as below

=sum({<is_complete_quarter={1}>}Sales)

[stevejoyce]

You can create a table with your list of quarters you want to keep (in your case where count of months per quarter = 3 (i.e. full quarter).

Then load your data table where quarter exists in the conditional table.

quarters_to_keep:
load distinct *
where month_count_perQuarter =3;
load distinct
quarter as quarters_to_keep,
count(distinct month) as month_count_perQuarter
resident data
group by quarter;

data_final:
noconcatenate load *
resident data
where exists (quarters_to_keep, quarter);

drop table data;

[jorn188]

This worked perfectly, thanks