Restrict results of new calculated items to certain rows only

fabryddorf-Oracle

Hi All,

I am creating a report using new calculated items on column level.

Actually very simple. $4-$3  (column 4-column3 ) is my calculated items, however I do not want to show the results on all the rows.

Can i restrict some rows to show the results?

Thanks!!

Gianni Ceresa

Hi,

A formula is a formula and execute what you wrote, so if you want to apply it only to some rows you need to write the conditions in the formula itself to say when it must be used or not. CASE WHEN is the most used approach for that.

fabryddorf-Oracle

Ciao Gianni,

thanks for your prompt answer. I am not an expert and though I know case functions, i wouldn't know how to use them in the new calculated items.

I will give a try though.

Thanks and Regards

Giuliano

[Deleted User]

a) Why do you use the $ notation and not actual named column formulas?

Because b) then your issue would just be solved by just a CASE WHEN

Gianni Ceresa

Joining Christian comment I suspect something a bit "weird", so if you post more details (I imagine a screenshot is asking too much) it will be easier to point you in the right direction more precisely.

fabryddorf-Oracle

fabryddorf-Oracle

This is what i am trying to do. The pickup column is based on new calculated items.

It is substracting the last two dates.

From ROOM vs YAGO onwards, I would like to show null under Pickup.

Would you build the case function in the new calculated items or would you create an additional column?

Thanks

fabryddorf-Oracle

I added screen shots. Hope this is more clear now.

Gianni Ceresa

I smelled a pivot and an intra-dimension calculation

How are the last 2 dates defined? Do they come from a prompt or from a variable or something?

You could use FILTER instead of $3 and $4, so you are sure about what happen

The thing with CASE WHEN is that the condition to say if the calculation must be done or not can't be "only before 'Room vs YAGO#' ", it must be a logical condition which would be like CASE WHEN "table"."column" IN ('...', '...', '...') THEN <calculation END

Where you replace "table"."column" with the column you used there and inside the IN () you add all the values of the column when the calculation need to be done.

fabryddorf-Oracle

Hi Gianni,

The dates are attributes of the snapshotdate dimension and are obtained with a timestamp function.

I tried the CASE function on just one of the columns to test , but i am getting a syntax error. I might doing a thinking mistake though.

Tried values in single quotes as well with same results.

Gianni Ceresa

My bad ... I was thinking at a column formula, not a calculated item. A calculated item just can't accept CASE WHEN, it only accept simple calculation between items.

That's because your "pickup" column isn't a measure or anything like that, it's just an extra item added into the values of the 'snapshot date' column.