topic Re: Calculated Dimension or Set Analysis in a Straight Table in QlikView

Calculated Dimension or Set Analysis in a Straight Table

cliff_clayman — Mon, 25 Jul 2016 15:49:06 GMT

I have a straight table with 3 fields. FullName, Purchase and PurchaseAmount. I need to Sum the Purchase and PurchaseAmount fields. I then would like to apply a condition on each field before I display the results. I would only like to see the results where the Sum(Purchase) < 5 AND where the Sum(PurchaseAmount) < 1000.

I have tried using Set Analysis and Calculated Dimensions, but I can't seem to get either way to work. I've attached some sample data if anyone can please provide a solution.

Re: Calculated Dimension or Set Analysis in a Straight Table

antoniotiman — Mon, 25 Jul 2016 16:01:06 GMT

I suggest You Calculated Dimension like

=Aggr(If(Sum(Purchase) < 5 and Sum(PurchaseAmount) < 1000,FullName),FullName)

and check 'Suppress when value is null'.

Regards,

Antonio

Re: Calculated Dimension or Set Analysis in a Straight Table

sunny_talwar — Mon, 25 Jul 2016 16:07:54 GMT

Or this using set analysis:

Dimension:

Name

Expressions:

=Sum({<Name = {"=Sum(Charge) < 5 and Sum(Amount) < 1000"}>}Charge)

=Sum({<Name = {"=Sum(Charge) < 5 and Sum(Amount) < 1000"}>}Amount)