Can we divide a column's data with another column's previous day data ?

Anshul Kashyap shared this question 6 years ago
Answered

Hello,

I have a requirement to divide a column's record with another column's previous date data. Do we have any option to perform this. Please suggest.


Regards

Anshul

Replies (4)

photo
1

Hi Anshul,

Can you elaborate on how this would work? Typically dates are not stored in number format so are difficult to do math on top of.

The best route for this is to use our pre-defined functions or freehand such as DATEDIFF to first make this date a real number which you can then do math on. (eg: days since ___).

Regards,

Nathan

photo
1

Hi Nathan,

Just to elaborate I have two columns A and B which are coming from DB by date (like A contains some data for whole month and B contains some other data for whole month). Now I want a third column C which contains division of A by B's previous day data (For ex: C equals A data of 11th October/ B data of 10th October)

Please suggest me if this can be done through Yellowfin.


Thanks

Anshul

photo
2

Hi Anshul,

Typically the best way to do this is to create an append sub-query joining on whatever these two columns have in common.

In the first sub-query, filter the data down to todays date, and in the second, filter it down to be yesterdays date.

You can also do this with calculated fields.

Once you have columns representing each day, you can just do basic division using a third calculated field.

Let me know if this makes sense.

Regards,

Nathan

photo
1

Hi Nathan,

I am not able to get data in calculated field for previous day . For Eg: "Case when date -1 then valueX End" but this expression is returns invalid clause error. Can you please help me over this condition ?

Thanks

Anshul Kashyap

photo
1

Hi Anshul,

Generally you cannot subtract integers from dates in the simple calculated field builder. I would recommend using a free-hand calculated field and then using a relevant DB function such as DATE_SUBTRACT.

Let me know if this makes sense.

Regards,

Nathan

photo
1

Hi Nathan,

I tried using DATE_ADD function and passing -1 as no of days to be added but it also didn't worked as expected. so can you share a small POC in which this demo can be explained properly ?Please suggest.

Thanks

Anshul Kashyap

photo
1

Hi Anshul,

I can put together a brief example, what type of DB (mysql, sqlserver, etc) are you reporting on?

Regards,

Nathan

photo
1

We are using PostgreSQL .

photo
1

Hi Anshul,

I think that the sub-query approach I recommended initially is the better option here so I have attached a video demonstrating this.

Let me know if this makes sense.

Regards,

Nathan

photo
photo
1

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan

Leave a Comment
 
Attach a file