Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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..
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
- Marcus
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
=(class(Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date))), 200)
This throws an error when used in dimension
Dear Rajesh,
can you share please sample data, QV apps and result.
Its helpful to understand you requriment.
Regards,
Zain.
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
No it doesnot work
I tried with this also it returns "-"
=Class((Aggr(Avg(TOTAL <Name> SUM(Handled)),Name,Date)),200)
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).
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.
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..