Grouping not filtering as expected

[#1]

I have a table that I'm loading into my application telling me what fruit I have eaten on what day.

I'm then LOADing this table and counting up values using a GROUP BY clause with the first table RESIDENT.  This tells me how many days I'm having 1 piece of fruit, how many days I'm having 2 pieces, etc.

Finally, I have a graph over these counted values.

When I select some fields from my first table, to only include apples, my group by table doesn't re-run the count.  So I'm not really seeing what I want.  I want to see how many days I'm having 1 apple, how many days I'm having 2 apples, etc.  Other fruit should be ignored.

I suspect that there's a better way to do the counting (maybe in the expression or dimension of the graph) but can't figure it out.  "Fix your source data" isn't really a solution either, as I don't have control over this - the first table contains a lot more detail.

Any help would be appreciated.  I've attached a sample application.

[vgutkovsky]

Your instincts are correct--there is a better way to do it, and that's to leverage the power of QlikView rather than taking everything to the script. Change your dimension to Day. Change your expression to: count(distinct ID)

Regards,

Vlad

[#3]

Thanks Vlad, but I'm afraid that's not quite right.  That shows me "on day 1 I had 3 pieces of fruit, on day 2 I had 1 piece of fruit...", but what I want is "There were 4 days where I had 1 piece of fruit, there was 1 day where I had 2 pieces of fruit..."

So I almost want one level above what you're saying - where I don't care what day I had 3 bits of fruit, but just how many days I hit that target.

Regards,

Richard

[vgutkovsky · Accepted solution]

Sorry, my mistake! In that case, you can create a calculated dimension, as follows:

aggr(

       count(distinct ID)

       ,Day

)

and make your expression: count(distinct Day)

Regards,

Vlad

[nilo_pontes]

Hi Richard,

Please take a look at the attached file.

Regards,

Nilo

[#6]

Hi Richard,

From what i understand You want to see how many fruits you have eaten on what day.

To be more precise, you might also want to see how many fruits of each kind you had per day..

Here is my solution:

i either count type of fruit per each day, or count basicly fruit per day and then use one of the results as your dimension... pls see the file and few proposals.

You can also use calculated dimension in the chart to get similar results .

If You need further help please provide for example a drawing how the final result should look like.

Regards

LM

[#7]

Excellent.  That's sorted now.  Thanks very much.

Now just need to figure out sorting the calculated dimension...

[vgutkovsky]

Sorting is simple enough is you want a basic low-to-high or high-to-low sort. Just go to the Sort tab.

Cheers,

Vlad