Why do I need aggregation for two if-statements?

[Applicable88]

Hello,

my original function is a little bit different and actually with a variable. But I'm using a simplified version to explain my problem, since the variable isn't the problem here.

I would think that this is pretty straight forward and right: 

=if(If(vVariable='0',count(distinct Orders),
count(distinct Orders2))>0,
weekname(StartingDate))

So basically it means, if there is a customer than count the Order otherwise count me the Order2. Either way if I have value bigger than zero than show me the Starting Date in calendar week form. I would say the syntax is absolutely fine and its pretty straigth forward.

My question is, since I already give the condition, to only show me the StartingDate as weekname format when value greater zero, why I need to aggregate again to the StartingDate to work? The StartingDate is already within if-statement one time.

Like this:

=aggr(if(If(vVariable='0',count(distinct Orders),
count(distinct Orders2))>0,
weekname(StartingDate)),StartingDate)

Now I get values, but I don't understand why it needs to be like that.

Thanks in advance.

Best. 

[fosuzuki]

Where you are using this if() expression? In a chart dimension, chart expression, chart condition, textbox?

Let's say you put weekname(StartingDate) in a textbox. If StartingDate has more than one possible value, Qlik will not know for which record you want the weekname, so the result of the expression will be null. This is because weekname() is not an aggregation function, and it expects a single value as input parameter. In this case, you would need to put some kind of aggregation like:

weekname(Min(StartingDate))

or

weekname(Max(StartingDate))

Hope this clarifies the issue.

[Vegar]

I think you are spot on @fosuzuki.

I just any to add one thing. You whenever you are namedropping a field name inside an expression without an aggregation wrapper around it like sum(), count(), max(), min(), etc. Qlik Sense intepret it as an equivalent to the "aggregation" function Only().

So when you write weekname(StartingDate)  it is interpreted as weekname(Only(StartingDate)) would have been interpreted. And as @fosuzuki are saying it will return null if more than one value is present. Therfore you need to aggregate the expression over a dimension that ensures your StartingDate to return a single value per aggregate dimension value. 

[Applicable88]

Hello @fosuzuki ,hello @Vegar ,

thank you both for giving me the quick reply. I know what you both mean, but still find a contradictory situation, which I want to show you in an example. First of all I use the aggr function not in a textbox. I understand that a textbox needs a unique value otherwise "null" , because qlik otherwise wouldnt know what to return.

But in my case I use the aggr function as a dimension in my bar-chart as well as in my table as a dynamic to swith between situation '0' and '1'.

Example:

I now added several if function to show its not the weekname() function which makes the failure.

Order	Status	Startdate	weekname(startdate)	aggr(function….	if(if(…....weekname(startdate)	if(if(…....startdate	if(order>0, weekname(startdate))
111111	K	18.06.2020	2020/25	2020/25	makes it invalid	also invalid	2020/25
222222	V	19.06.2020	2020/25	2020/25			2020/25
333333	V	22.06.2020	2020/26	2020/26			2020/26
444444	K	24.06.2020	2020/26	2020/26			2020/26

Going back to my example, I have a dynamic bar chart and a dynamic table, where I use a Variable  succefully to switch only orders with  either Status K or V.

Simplified here again (and strangely only that seems to work): 

=aggr(if(If(vVariable='0',count({<Status={"K"}>]Orders),
count({<Status={"V"}>]Orders)))>0,
weekname(StartingDate)),StartingDate.autocalendar.date)

I can show that the weekname isn't the problem here, because I also gets invalid value without weekname and without aggr:

if(If(vVariable='0',count({<Status={"K"}>]Orders),
count({<Status={"V"}>]Orders)))>0,
StartingDate.autocalendar.date))

The question is basically:

since the follwoing two are working in table and bar-chart as well:

1.      if( Order>0, weekname(StartDate.autocalendar.date)   returns me value correctly,even with weekname and without aggr.

2.    weekname(Stardate.autocaleandar.date) same as example no.1

so why I need aggr to make that work: 

if(If(vVariable='0',count({<Status={"K"}>]Orders),
count({<Status={"V"}>]Orders)))>0,
StartingDate.autocalendar.date)  ?

Besides the variable and the nested double if-statement, that function isn't so far fetched from that:

if( Order>0, weekname(StartDate.autocalendar.date)

And I'm pretty sure it has nothing to do with the weekname function. 

I hope once more you understand my issue understanding that. I hope you know what I mean. 

Thanks.

[fosuzuki]

Hi,

So I understood what you are saying, but I can't see anything wrong in what you described.

Maybe there is something else in the data model structure. Can you share the app?

[Applicable88 · Accepted solution]

Hello @fosuzuki ,

Thank you for asking. I did many repositioning of  the function to see how it reacts in a straight table. I think I know where my misunderstanding was. It was important to clarify that the function and syntax wasn't wrong.

As far as I know, the weekname() function was not the problem. Since it was not put in a kpi window, but a bar chart....or straight table.  So the question was why the  following function  does not return anything:

=if(If(vVariable='0',count(distinct Orders),
count(distinct Orders2))>0,
weekname(StartingDate))

Not because of the weekname, but because it was labled as dimension. But when you put it as a measure in a straight table it really well can also return the weekname, without aggr().

But if I want to put it as a dimension, then I have to write it like:

 =aggr(if(If(vVariable='0',count(distinct Orders),
count(distinct Orders2))>0,
weekname(StartingDate)), StartingDate)

I just was surprised that I can put a measurement like a "dimension like" format into the table. The difference is just that the first function also gives you a "Total" Value, which you can see in the first line of a straight table like: 

Thanks for your help!