Conditional statement in Dimension

[#1]

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

[marcus_sommer]

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

[stevedark]

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

[#4]

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

This throws an error when used in dimension

[#5]

Dear Rajesh,

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

Its helpful to understand you requriment.

Regards,

Zain.

[marcus_sommer]

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

[#7]

No it doesnot work

I tried with this also it returns "-"

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

[stevedark]

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).

[#9]

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.

[stevedark · Accepted solution]

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..