Assist with Expression - possibly combo of Firstsorted value and Aggr()?

[QFanatic]

hi guys,

I will try my best to describe my scenario.

I am trying to mimic a SQL Union in my Chart....

select min(Period), cookie from

(select distinct cookie,period,....1 as Cookie_1 from T1  where....abc

union

select distinct cookie,period,....1 as Cookie_2 from T1  where....def...), group by cookie.

In other words, I always just want to display (and count) a cookie once - where min period is applicable.

so what needs to be in the expression is..

1. (if(RangeSUM(COOKIE_1,COOKIE_2) > 0,
COUNT(distinct COOKIE))  ....this will count all the cookie_1 and cookie_2 values. This can return multiple periods in the chart if a cookie exists in more than one month....which I dont want.

2. Now I need to see, in the preceding statement, what is the MIN(Period) and then count the cookies once only for that  min period.

3. I am totally confused now if and where I should use Count (to get total) and or Aggr (to aggregate by Period and cookie)

In the attached model, I've selected 1 Cookie - and that should only be counted in 201910, as that is the min month.

any help appreciated thanks.

[Brett_Bleess]

I would recommend browsing around in the Design Blog area of Community to see if you can find some posts there that will help you figure out which the best way to go with things may be.  I was not sure exactly what you need, so just giving you the base URL, there is a search box there you can use to search just that area, and there are over 700 posts there and most of them are how-to related, so it should be helpful, but the trick may be finding the one(s) you need.  I would probably search on AGGR and check those out first, and then see if that gets you into other areas that might work better.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have something better, but this will kick things back up, so someone else may have a look and offer something more direct for you, I am just not very good with the development questions, sorry.

Regards,
Brett

[QFanatic]

Thank you

[sunny_talwar · Accepted solution]

I am not entirely sure, but I think you need this

Sum(Aggr(
  If(COOKIE <> Above(TOTAL COOKIE), 1, 0)
, COOKIE, (PERIOD, (NUMERIC))))

[QFanatic]

Thank you Sunny,

Just a question - why the "Numeric" in your statement?

Thanks again

[tresesco]

'Numeric' is value to StructuredParameter argument of aggr(). This lets you define the sort order.

In the StructuredParameter argument in the Aggr function, you can specify sorting criteria on the dimension in your expression. This way, you impose a sort order on the virtual table that is produced by the Aggr function.
The argument StructuredParameter has the following syntax:
(FieldName, (Sort-type, Ordering)) 
Structured parameters can be nested:
(FieldName, (FieldName2, (Sort-type, Ordering))) 
Sort-type can be: NUMERIC, TEXT, FREQUENCY, or LOAD_ORDER.

Read more here:  https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/ChartFunctio... 

[QFanatic]

Sunny,

Another thing - you're testing for Cookie <> Above Cookie , when Cookie is not a dimension in the Table?

thanks

[tresesco]

---

@QFanatic wrote:

Sunny,

Another thing - you're testing for Cookie <> Above Cookie , when Cookie is not a dimension in the Table?

thanks

---

Again from a non-Sunny, I know this time of the day/night Sunny is not active in the community (now-a-days). Probably sun at Sunny's world is not yet very sunny right now. So I dare to try. 😋

Aggr() creates a virtual table and that let's you use Above() even if you don't have that dimension in your chart.  

[QFanatic]

Thank you!