12 Replies Latest reply on Jul 21, 2018 4:08 PM by Shinichiro Murakami

# Search Date and Differences

Hi all

May I know how to create this?

I would like to have the user to select their preferred date ( Search Date). While it will calculate the differences based on the selected date.

The selected date is 1/8/2018

The criteria for the differences is to take the nearest date of selected date to compare.

Since there is no data in 1.6.2018 and 1.7.2018 so it will take 1.5.2018

Thanks

• ###### 1. Re: Search Date and Differences

HI Haan

Hope this works.

Thanks,

Shin

• ###### 2. Re: Search Date and Differences

Dear Shin

Thanks

May I know how to do the differences based on the criteria ?

And what is the meaning of the below calculation field created?

{fixed:max( if [Date]<[Search Date] then [Date] end)}

• ###### 3. Re: Search Date and Differences

Hi Haan,

I am sorry I don't understand the logic of difference.

Could you clarify?

Here is a help of LOD expressions. {fixed;~}

Thanks,

Shin

• ###### 4. Re: Search Date and Differences

Dear Shin

Means that the calculation field meant "if the searched date is more than the date, it will present the max date in the date?"

What I meant of the differences is like normal calculation of differences

-(today minus yesterday) / yesterday

But in my example above, it is a special case

If let say selected date is TODAY but yesterday is do not have data(due to public holiday)

Hence, the date for YESTERDAY need to take the latest date available.

Do you understand?

• ###### 5. Re: Search Date and Differences

Means this ?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: Search Date and Differences

Dear Shin

I am applying your approaches to my workbook.

I faced a problem if i wanted to replace it to a calculation.

Could u pls help me?

Thanks

• ###### 7. Re: Search Date and Differences

Dear Shin

1. Could you please explain the calculation field of "Calculated Date by Name"?

I cant get it....

2. For the calculation field of "Calculated Date by Name"

{fixed [Name1]:max( if [Date]<[Search Date] then [Date] end)}

We have to fill in measure name in the [Name1] ?

As in one of my sheets, i do not have measure name...it is in measure value as it is in excel format.

Thanks

• ###### 8. Re: Search Date and Differences

HI Haan,

Try this.

If the Previous date is different from Name, need to add "name" in LOD expressions.

Thanks,

Shin

• ###### 9. Re: Search Date and Differences

Dear Shin

After applying MIN, it appeared blank...no numbers...

• ###### 10. Re: Search Date and Differences

Maybe null-handling is needed.

Can you attach sample book with errors?

Without seeing data, it's way inefficient.

if in([date]) = [search date] then

((zn(sum (if [name] = ""KL" then [amount] end)/2) / zn(sum([amount]))

end

Thanks,

Shin

• ###### 11. Re: Search Date and Differences

Dear Shin

Here u go...it is located at sheet 4.

Thanks a lot really!

• ###### 12. Re: Search Date and Differences

There is no 1/8 in your data.

It's difficult to understand what you want to achieve from multiple data sources (which makes things very complicated),

anyways adding "date" from secondary source and pick existing date. (not 1/8)

Thanks,

Shin