Set Analysis with Character dimensions

[shyamcharan]

Hi Experts,

Despite of looking at several example I still keep getting this question.

Set Analysis: Can we use the Set Analysis with Character dimensions?

If yes, can anyone please provide me information on any example that I can find.

This might be a very dumb question. I sincerely apologize for the guys who spend time on more important questions.

But your answers do help.

Thanks heaps in advance.

Regards,

Shyam.

[jagan]

Hi,

What do you mean by Character Dimensions? 

You are looking for this

=Sum({<Country={'India'}>} Sales)

=Count({<Product={'A'}>} OrderNo)

=Count({<A={'x', 'y', 'z'}>} OrderNo)

Regards,

Jagan.

[shyamcharan]

Hi Jagan

Thank you for your reply.

Character Dimensions are the character field in the table.

If I had to display a value from a character field and use the SETS, how that would be?

Definitely, I cant use Sum() or Count() all the time right?

Thanks in advance.

Regards,

Shyam.

[jagan]

HI,

Try this functions

=Only({<Product={'X'}>}DimensionName) -- To use this function you have only one possible value for the selectiosn, otherwise it will give you Null

=MaxString(DimensionName)

=MinString(DimensionName)

Check help file for detailed help.

Hope this helps you.

Regards,

Jagan.

[shyamcharan]

Hi Jagan,

I am getting error when I try with the functions you have mentioned.

Not sure what i am missing.

Can you please help me with that.

Basically I want the Equipment number to be shown for the Expiry date that are null.

Please find the attached file.

Thanks in advance.

Regards,

Shyam.

[jagan]

Hi,

Try like this

Dimension: EQUIPMENT_NUMBER

Expression =Sum({<EXPIRY_DATE-={'*'}>} EQUIPMENT_COUNT)

Hope this helps you.

Regards,

Jagan.

[jyothish8807]

Hi Shyam,

Over here your field expiry date dosent have any value.If you put it in a list box its not giving any value.Fix that isue first then try like this:

Dimension:

=only({<EXPIRY_DATE-={'*'}>}EQUIPMENT_NUMBER)

expression:

Count(EQUIPMENT_ID)

Regards

KC

[shyamcharan]

Hi Jyothish,

Thank you for your inputs. I have changed the column now from Expiry date to some other column that has data.

But still I get error in calculated dimension.

Can you please suggest further.

Thanks,

Shyam.

[shyamcharan]

Hi Jagan,

Thanks for your reply.

What if I donot have any numeric column to use it in SUM()?

How do i approach then?

Thanks in advance.

Regards,

Shyam.

[jonathandienst]

Hi

Unfortunately you cannot find null values with set analysis. You have these options:

• Use Sum(If()) expression, something like (if in a table or chart)

    =MaxString(If(IsNull(ExpiryDate), EquipmentNumber))

• Use NullAsValue to make the null value into an empty string, and then

    =MaxString({<ExpiryDate = {''}>}, EquipmentNumber)

• Set a null flag value during your load, like this:

     LOAD ...

          EquipmentNumber,

          ExpiryDate,

          If(IsNull(ExpiryDate), 1, 0) As NullDate,

          ...

     Then use this set expression

    =MaxString({<NullDate= {'1'}>}, EquipmentNumber)

HTH

Jonathan