15 Replies Latest reply: Aug 5, 2015 9:14 AM by Rajesh V

# Conditional statement in Dimension

Hi All,

I want to use the belwo IF stamnt as one of the dimension along with Name in a pivot chart.

=if((Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date)))<600,'0-600',if((Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date)))<800,'600-800',if((Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date)))<1000,'800-1000',if((Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date)))<1500,'1000-1500','1500>'))))

How do i do it.

It works as an expression in a Table and i want that as dimension.

Or is it possible  to add it as Variable and then in to dimension

• ###### Re: Conditional statement in Dimension

I suggest that you have a look on the function class() which returned buckets of the expression-result. Unfortunately it worked only with sequentielly bucket-size uncomplicated - with nested if could be this changed. But the aim is normally to avoid nested if's therefore the use of a kind of lookup with pick(match()) worked often smoothly.

Which conditional functions do you use?

class(Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date))), 200)

Pick(match()) examples:

Re: Multiple nested if statements

Re: Substitute to nested ifs

- Marcus

• ###### Re: Conditional statement in Dimension

=(class(Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date))), 200)

This throws an error when used in dimension

• ###### Re: Conditional statement in Dimension

Yes, it's not correct. I have forgotten to implement what stevedark mentioned that calculated dimensions needs to be wrapped in an aggr-function also if there are already aggr-functions inside the expression, try:

class(agrr(Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date)), Name), 200)

- Marcus

• ###### Re: Conditional statement in Dimension

No it doesnot work

I tried with this also it returns "-"

=Class((Aggr(Avg(TOTAL <Name> SUM(Handled)),Name,Date)),200)

• ###### Re: Conditional statement in Dimension

That code is not working as you have an Avg and a Sum without an Aggr

separating them.

I'm not sure what the Avg is adding here. Try stripping out the Avg, Total

and corresponding end bracket.

Did you try wrapping your original function in another Aggr, as I suggested

previously? What result did this give? Did you also try a single Aggr

around the outside, rather than separate ones?

Class is quite a blunt tool, as the band have to be equal, and the display

of the values is not ideal (though this can be fixed up with a replaced

statement).

• ###### Re: Conditional statement in Dimension

Hi,

You mean this way

class(agrr(Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date)), Name), 200)

I am computing Avg & Max for the given date Range for the Names.

SO it is required. Finally I need to bucket the name under and show the count locationwise.

• ###### Re: Conditional statement in Dimension

I can't see how what you have now relates back to your original query.  The size of the buckets for one does not seem to match your requirement?

You have a typo in your expression above, with aggr spelt wrong (agrr) in one place.

That code seems that it will give the highest value of any day for each name, and then bucket that value in groups 0-200, 201-400,401-600 etc.?

Also, for a dimension the outer part should usually be an Aggr, i.e. the statement should start with Aggr.  Otherwise you will only get a single value for the dimension or a null..

• ###### Re: Conditional statement in Dimension

You may want to read my description of Aggr from the 31st of last month on this thread:

Aggr function on avg

• ###### Re: Conditional statement in Dimension

I am getting the Avg & MAx based on the aggr function same as your example in table.

But how do i bucket them and show it in Graph as dimension with count of Names under each bucket

• ###### Re: Conditional statement in Dimension

Yes you are right there was a typo and it is working fine.

But instead of calls as it is grouping in predefined intervals is there a possibility of getting the bucket predifined as using IF condition in dimension

• ###### Re: Conditional statement in Dimension

To do this you will need to use your original IF statement,  and nest Aggrs

accordingly.

• ###### Re: Conditional statement in Dimension

But I am unable to add the below Script in teh dimension of Grpah

if((Avg(TOTAL <Name> Aggr(SUM(Handled),Name,Date)))<400,'0-400',if((Avg(TOTAL <Name> Aggr(SUM(Handled),Name,Date)))<800,'400-800',if((Avg(TOTAL <Name> Aggr(SUM(Handled),Name,Date)))<1000,'800-1000',if((Avg(TOTAL <Name> Aggr(SUM(Handled),Name,Date)))<1500,'1000-1500','1500>'))))

It says Error in Calcualtion

• ###### Re: Conditional statement in Dimension

Got It Thanks

• ###### Re: Conditional statement in Dimension

Generally for dimensions you need to have the whole expression wrapped in an AGGR, in order that QlikView knows which rows to attach to which of the dimensions.

You may find wrapping the whole lot in an AGGR(  , Name, Date) function may work.

Given that, you may then also be able to simplify things, a lot, by removing each of the separate AGGR statements you already have, and simply have one AGGR around the outside.

Hope that helps.

Steve

• ###### Re: Conditional statement in Dimension

Dear Rajesh,

can you share please sample data, QV apps and result.

Its helpful to understand you requriment.

Regards,

Zain.