Is there a good way to exclude incomplete values?
Dimension 1: TimePeriod - Cyclic Dimension with 7 day groupings, 28 day groupings, 54 day groupings, etc.
Dimension 2: Category - A, B, C, etc.
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.
Solved! Go to Solution.
I'm afraid I don't quite understand. Can you explain with example data? A demo qlikview document would be helpful.
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.
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))
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.
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.
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:
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!