topic Re: Group by dimension in QlikView

Group by dimension

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I'm trying to achieve No of week launch by article level with below expected output format, can you advise me. Thanks

No of week launch:

=Aggr((Max(maxSalesDate)-min(minSalesDate))/7,ArticleNo2)

Re: Group by dimension

sunny_talwar — Thu, 27 Oct 2016 16:17:54 GMT

You can try this:

=Aggr(NODISTINCT(Max(maxSalesDate)-min(minSalesDate))/7,ArticleNo2)

Re: Group by dimension

sunny_talwar — Thu, 27 Oct 2016 16:19:23 GMT

Or a better solution would be this:

=(Max(TOTAL <ArticleNo2> maxSalesDate)-min(TOTAL <ArticleNo2>minSalesDate))/7

Re: Group by dimension

jayanttibhe — Thu, 27 Oct 2016 19:04:42 GMT

Hi,

Please note that - NODISTINCT Vs TOTAL - there is huge diff. I.e. if there are more than 1 instance of the chart dimensions - in this case suppose 2 Rows of the dimensions as mentioned following in data model - then NODISTINCT take account the Facts associated with those separately. However, this is not the Case with TOTAL.

ArticleNo2	Launch	Color
20180010	Winter 2015	BLACK
20180010	Winter 2015	BLACK

Re: Group by dimension

sunny_talwar — Thu, 27 Oct 2016 19:22:03 GMT

I am not sure I understand the difference between NODISTINCT and TOTAL here. Can you may be elaborate?

Re: Group by dimension

jayanttibhe — Thu, 27 Oct 2016 19:44:35 GMT

Hi Sunny,

Please find attached example to elaborate my point.

Re: Group by dimension

sunny_talwar — Thu, 27 Oct 2016 20:21:12 GMT

I guess I wouldn't use Sum around the aggr() function if I plan to use NODISTINCT, So as long as you are not looking to get a total (which in case of straight table can be achieved using Sum of Rows under Total mode, I will always get the same result for row wise data

No are these two functions different? Yes.

Do they have different purposes? Yes

Can one replace the others? Not always, but in this case it can. Also, I would suggest to use TOTAL over NODISTINCT for this particular case

Re: Group by dimension

jayanttibhe — Thu, 27 Oct 2016 22:12:24 GMT

Hey Sunny,

I agree with you. But when Set Analysis comes in play, things become bit complicated.

Quoted swuehl

"The set analysis expression in your inner aggregation function does not define the set of possible values for the AGGR dimension fields. If you need to modify this set (i.e. you want a different set than defined by the selections), you need to use a second set expression in the outer aggregation function."

I always prefer to wrap AGGR with the appropriate Aggregate Function (Most of the cases the Inner aggregate function)

This gives a greater flexibility and accurate results when we amend the expressions with set analysis.

In short, I will never prefer to use the AGGR function without wrapping up the aggregate function

Thanks

Jayant

Re: Group by dimension

sunny_talwar — Thu, 27 Oct 2016 23:28:21 GMT

Then why only wrap with Sum my friend? Why not Only or Avg or Min or Max? What made you choose Sum?