topic Re: Only Show Complete Values in QlikView

Only Show Complete Values

Fri, 01 May 2015 07:35:41 GMT

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.

Re: Only Show Complete Values

Gysbert_Wassenaar — Fri, 01 May 2015 15:59:11 GMT

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

Re: Only Show Complete Values

Fri, 01 May 2015 17:12:44 GMT

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

sunny_talwar — Fri, 01 May 2015 17:40:50 GMT

Try this expression:

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

Also please find attached your sample.

Best,

Sunny

Re: Only Show Complete Values

maxgro — Fri, 01 May 2015 17:42:57 GMT

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

Re: Only Show Complete Values

Fri, 01 May 2015 18:17:31 GMT

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

Re: Only Show Complete Values

sunny_talwar — Fri, 01 May 2015 18:19:28 GMT

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

Re: Only Show Complete Values

Fri, 01 May 2015 18:29:58 GMT

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

sunny_talwar — Fri, 01 May 2015 18:41:36 GMT

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

Re: Only Show Complete Values

Fri, 01 May 2015 19:04:40 GMT

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!

Re: Only Show Complete Values

sunny_talwar — Fri, 01 May 2015 19:24:55 GMT

You need a TOTAL to get it to work:

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