topic Re: Conditional statement in Dimension in QlikView

Conditional statement in Dimension

Tue, 04 Aug 2015 13:18:50 GMT

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

marcus_sommer — Tue, 04 Aug 2015 13:38:14 GMT

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

stevedark — Tue, 04 Aug 2015 13:54:59 GMT

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

Wed, 05 Aug 2015 04:49:56 GMT

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

This throws an error when used in dimension

Re: Conditional statement in Dimension

Wed, 05 Aug 2015 06:12:05 GMT

Dear Rajesh,

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

Its helpful to understand you requriment.

Regards,

Zain.

Re: Conditional statement in Dimension

marcus_sommer — Wed, 05 Aug 2015 06:38:49 GMT

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

Wed, 05 Aug 2015 07:30:05 GMT

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

stevedark — Wed, 05 Aug 2015 08:15:13 GMT

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

Wed, 05 Aug 2015 08:22:38 GMT

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

stevedark — Wed, 05 Aug 2015 08:54:10 GMT

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

stevedark — Wed, 05 Aug 2015 08:57:18 GMT

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

Wed, 05 Aug 2015 09:26:40 GMT

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

Wed, 05 Aug 2015 10:16:29 GMT

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

stevedark — Wed, 05 Aug 2015 10:45:44 GMT

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

accordingly.

Re: Conditional statement in Dimension

Wed, 05 Aug 2015 13:00:17 GMT

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

Wed, 05 Aug 2015 13:14:38 GMT

Got It Thanks