Day by day calculation

[#1]

Hi All,

I have a table with the following fields: Brand, Post_Date, Count(Id). To each date I want to calculate the count of the previous day.  I tried several thing with no luck. I only managed to calculate for today()-several days.

See calculation below:

= count(TOTAL {<Post_Date = {"$(=Date(today()-9))"}>} Id)

I want to replace the Date(today()-9)) to something like Post_Date-1.

Any ideas?

Thx,

Hila.

[Gysbert_Wassenaar]

Maybe you can use the above function: = above(count(Id))

What won't work is a set analysis expression. The set is calculated at the chart level, not the row level.

[#3]

Thanks for your replay Gysbert!

We tried that option and it didn't work because not all records are displayed.

[#4]

Hi,

try this

= count( {<Post_Date = {"$(=Max(Post_Date)-1)"}>} Id)

[#5]

Thank Rakesh but it's not the answer.

I actually tried to do the calculation of the day before in another column (Post_Date - 1) and to use Column(1) in the set analysis but I haven't succeeded yet..

Thanks for you help.

[ashwanin]

Hi Hila,

Can you post some sample data.

[Gysbert_Wassenaar]

In that case you'll have to create an additional table in the script. See attached qvw.

[#8]

Hi Gysber!

Thanks for the great qvw

I was actually trying to avoid duplicating the table since it is very big.

I hope to find a solution in set analysis..

Thanks!!

Hila.

[#9]

Hi All,

I managed to get some progress with the following calculation:

= count(  {<Post_Date = {"$(=Date(Max(Post_Date)-1))"}>} Id)

The issue is I get the solution only for the max(Post_Date). When I tried to remove the MAX I don't get any result.

ideas?

Thx!

[Gysbert_Wassenaar]

You don't have to duplicate the table. All you need to do is add a table that links each date with the day before. If you have 365000 different date values in your fact table, i.e. about a thousand years, then your AsOf table will contain twice that number or 730000 records. But your fact table, with possibly million of records, will not change at all.