topic Re: Re: Grouping not filtering as expected in QlikView

Grouping not filtering as expected

Mon, 30 Jun 2014 20:40:41 GMT

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.

Re: Grouping not filtering as expected

vgutkovsky — Mon, 30 Jun 2014 21:31:42 GMT

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

Re: Grouping not filtering as expected

Mon, 30 Jun 2014 21:42:17 GMT

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

Re: Grouping not filtering as expected

vgutkovsky — Mon, 30 Jun 2014 21:45:08 GMT

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

Re: Re: Grouping not filtering as expected

nilo_pontes — Mon, 30 Jun 2014 21:46:56 GMT

Hi Richard,

Please take a look at the attached file.

Regards,

Nilo

Re: Grouping not filtering as expected

Mon, 30 Jun 2014 21:56:35 GMT

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

Re: Grouping not filtering as expected

Mon, 30 Jun 2014 21:56:53 GMT

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

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

Re: Grouping not filtering as expected

vgutkovsky — Mon, 30 Jun 2014 21:59:24 GMT

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