topic Re: Aggr () inside if in QlikView

Aggr () inside if

jduluc12 — Thu, 05 Oct 2017 12:26:58 GMT

Hi,
I am writing this expression

if (Product.Amount > 0 and aggr(count(distinct Product.SalesDates),Product) > 0, Product.Name)

then it works

but when i do

if (Product.Amount > 0 and aggr(count(distinct Product.SalesDates),Product) = 0, Product.Name)

then it does not work.

The data is available for the second expression also.

why?

Re: Aggr () inside if

Chanty4u — Thu, 05 Oct 2017 12:30:16 GMT

try below

aggr((count(distinct Product.SalesDates) = 0),if (Product.Amount > 0),Product.Name)

Re: Aggr () inside if

Peter_Cammaert — Thu, 05 Oct 2017 12:33:25 GMT

Can you describe the context of this expression? If it's used in a straight table, please specify which dimensions are used.

Re: Aggr () inside if

jduluc12 — Thu, 05 Oct 2017 12:35:24 GMT

It has the same problem,

It works when i use >0 and does not work when i use =0

Re: Aggr () inside if

jduluc12 — Thu, 05 Oct 2017 12:36:23 GMT

It is in a pivot table.

The dimension used is Product.Name.

Re: Aggr () inside if

Peter_Cammaert — Thu, 05 Oct 2017 12:41:46 GMT

Why do you use aggr() when a Product selection is being made by the dimension anyway?

If PRoduct.Amount is connected to Product.Name is connected to Product in a 1:1 fashion, and each Product may have zero or more SalesDates, then what about this expression:

IF ((Product.Amount > 0) and (Count(DISTINCT Product.SalesDates) = 0), ...)

Re: Aggr () inside if

jduluc12 — Thu, 05 Oct 2017 12:45:36 GMT

I think count needs aggr() else it shows "error in the calculated dimension"

Re: Aggr () inside if

Peter_Cammaert — Thu, 05 Oct 2017 13:18:33 GMT

So you are actually using this expression in a Dimension? Not just the Product.Name as a dimension?

Please describe what it should produce as dimension values. Thanks.

Re: Aggr () inside if

jduluc12 — Fri, 06 Oct 2017 10:58:09 GMT

you are right.

this is dimension. the dimension value should be the product name which does not have any sales date associated with it.

Re: Aggr () inside if

Peter_Cammaert — Fri, 06 Oct 2017 11:20:47 GMT

And what is the actual expression? You're sure you can't reduce the set of available Product.Names to those without sales by using set analysis in your expression?

Re: Aggr () inside if

pradosh_thakur — Fri, 06 Oct 2017 12:20:31 GMT

are you sure it has data ? because logically if there is no sales date there should be no amount sold . may be problem is with Product.Amount > 0 . try remove that and check. Just a guess.

regards

Pradosh

Re: Aggr () inside if

jduluc12 — Fri, 06 Oct 2017 12:36:09 GMT

Product.Amount is balance which can be there becasue of previous transactions.

Re: Aggr () inside if

swuehl — Fri, 06 Oct 2017 13:47:18 GMT

IMO, 'it does not work' is not a good issue description.

To understand the issue, it's necessary that you add details about your data, model, context of the calculated dimension (other dimensions / expressions? ) and also importantly, about your expectation as user / developer (what you expect to see given the still-to-defined input and context) and the observed result.

Please add more details to make the issue resolving more efficient.

If possible, add sample data / a small sample Qlik application demonstrating your issue.