Only Show Complete Values

[#1]

Is there a good way to exclude incomplete values?

Example:

Dimension 1:  TimePeriod - Cyclic Dimension with 7 day groupings, 28 day groupings, 54 day groupings, etc.

Dimension 2:  Category - A, B, C, etc.

Expression:  Sum(Pandas)

Date: YYYY-MM-DD

I only want time periods in which I see pandas every day--regardless of category.

if(aggr(count(distinct Date),TimePeriod)=max(aggr(max(Date),TimePeriod)-aggr(min(Date),TimePeriod)+1),sum(Pandas))  doesn't do the trick.

[Gysbert_Wassenaar]

I'm afraid I don't quite understand. Can you explain with example data? A demo qlikview document would be helpful.

[#3]

Thanks for responding.  Attached is an example with data.

Because the time periods include dates without any records, I'd like to see the top chart without the 1st and 3rd bars.

[sunny_talwar]

Try this expression:

=If(Max(TOTAL <TimePeriod> Aggr(Count(Date), TimePeriod, Category)) = 7, Sum(Pandas))

Also please find attached your sample.

Best,

Sunny

[maxgro]

first period has 1 date only and pandas in that date (= pandas in all dates); why you want to exclude?

// this exclude the first perod

if(count(TOTAL <TimePeriod> DISTINCT Date)=7, sum(Pandas))

// this  doesn't

if(count(TOTAL <TimePeriod> DISTINCT Date)=(max(TOTAL <TimePeriod> Date)-min(TOTAL <TimePeriod> Date)+1), sum(Pandas))

[#6]

What can I put in place of "7" if I don't know the full period length?

[sunny_talwar]

Well I assumed that each time period would be a max of 7 days so that's why I hard-coded it to be 7. You will need to tell me what it else it could be because I don't understand the logic behind what you are trying to do. I am just doing what you wanted.

Best,

Sunny

[#8]

I'm interested in cyclic groups of any length.  7, 14, 28, 30, etc.  I don't want partial periods at the beginning or end of my reporting window.  I also want get rid of the remainders that don't fit cleanly into a year-over-year analysis for example. 

[sunny_talwar]

So basically you want to create a dynamic time period which you can cycle through? Is this the correct assessment of what you are looking for?

Best,

Sunny

[#10]

Yes.  I have done that.

This gets me what I want for 7 day periods:

if(count(TOTAL <TimePeriod> DISTINCT Date)=7,sum(Pandas))

If I put this in the title it correctly shows the max period length:

max(aggr(count(distinct Date),TimePeriod))

However, this doesn't work:

if(count(TOTAL <TimePeriod> DISTINCT Date)=max(aggr(count(distinct Date),TimePeriod)),sum(Pandas))

Thanks for your assistance and patience!