how o remove null dates in date column

[Anonymous]

Hi Team,

i have one  date dimension column and one progress metric column

metric  was created in variable

let us ex:

progress=if(no.ofusers<>o,(no.ofusers/Totalusers),0)

using these two columns i want to create  "DATEofvalues"

condition is:

if particular date have some value then i want to show that date in "DATEofvalues"Column.

other wise i dont want to see that date in "DATEofvalues"Column.

ex:if i have a date like 4/20/2016 12:00:00 pm.if this date having any "progrees" metric value then i want to display that date

if that date dont have any values then i dont want to display the date in "DATEofvalues"Column

note:

for getting "DATEofvalues" i dont want use any charts or any objects and i dont want to do in Script.i need that "DATEofvalues" columnn in variable level.if  we need to change in script level then i need "date" coumn and i need "DATEofvalues"  both

regards,

Brahma.

[nico_ilog]

The logic is this:

LOAD

    DateField as yourDateField, <-- LOAD the Date field, un-touched so that you have ALL the dates

    if(progress = 0,DateField) as DATE_with_noProgress <-- LOAD the Date fields, where the corresponding progress columns are 0 ONLY. Then you have all the dates in the DATE_with_noProgress dimension. The other dates, that do have progress are no included on the field.

    if(progress > 0,DateField) as DATE_with_Progress <-- LOAD the Date fields, where the corresponding progress columns are more 0 ONLY. Then you have all the dates in the DATE_with_Progress dimension. The other dates, that do not have progress are no included on the field.

See attached qvw for example.

[Anonymous]

Excellent Nico Manro Oberholzer

but i need some more clarification on "DATE_with_noProgress".please check attached QVW file.

so i need to remove null date  DATE_with_noProgress. (null date means helighted in above screen shot)

Note:

in chart properties -->we can we "supress null","supress missing" some thing like i dont want to use that option.

as of i know  "supress null","supress missing" some thing is also available in  chart properties-->Dimentation-->so i dont want to use this options as well

so with out using above options how to show the  "DATE_with_noProgress" column  in pivots and charts with out "NULL"

let me know if you need any information.

Regards,

Bobs

[nico_ilog]

Hi Bob,

I think the suppress NULL in the dimension properties was created for this exact use case.

Nevertheless, See attached.

I changed the way in which the Data is displayed into the chart, expression side.

Let me know if it works for you.

Otherwise i would recommend building in FLAGS into the load script.

Nico

[Anonymous]

Nope

nico_ilog

let me give some idea :

EX:

if(PROGRESS = 0,DATE) as DATE_with_no_Progress,

in above condition if PROGRESS = 0 then  it wil display DATEs....if PROGRESS  not equal to Zero,then it will display "zero" or '-'...some thing like bellow you can see in screen shot.so i want to restrict or delete or truncate the '-' value in column level only

note:

in table or chart level i dont want use "supress" or "null missing" or any hiding null options

regards,

bBBy

[nico_ilog]

I Honestly do not think that it's possible.

I tried adding this into the load but still does not work.

The only other thing you can try and do is in the [Presentation] tab, where it says use the following symbol to represent NULL values and it has "-".

Remove that symbol and leave it blank then it will not show a character.

Nico

[Anonymous]

nope ..see already i mentioned i dont want do  any thing in charts or pivot level.

i want to put that restrict in column Level  only ...

is there any other options Nico Manro Oberholzer????

Regards,

Bobb

[nico_ilog]

I am not too sure.

Depending on the business case of your QV implementation, i would try a different approach to the problem as seen in the attached.

Other than that, i'm out of ideas.

Nico

[Anonymous]

any way some time we will definitely get answer.thanks Nico Manro Oberholzer for spending your time on my issues and you gave good ideas as well