Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Only Show Complete Values

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.

1 Solution

Accepted Solutions

Re: Only Show Complete Values

You need a TOTAL to get it to work:

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

10 Replies
MVP & Luminary
MVP & Luminary

Re: Only Show Complete Values

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


talk is cheap, supply exceeds demand
Not applicable

Re: Only Show Complete Values

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.

Re: Only Show Complete Values

Try this expression:

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

Also please find attached your sample.

Best,

Sunny

MVP
MVP

Re: Only Show Complete Values

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

Not applicable

Re: Only Show Complete Values

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

Re: Only Show Complete Values

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

Not applicable

Re: Only Show Complete Values

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. 

Re: Only Show Complete Values

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

Not applicable

Re: Only Show Complete Values

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!