topic Re: Day by day calculation in QlikView

Day by day calculation

Thu, 13 Nov 2014 11:57:34 GMT

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.

Re: Day by day calculation

Gysbert_Wassenaar — Thu, 13 Nov 2014 12:00:52 GMT

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.

Re: Day by day calculation

Thu, 13 Nov 2014 12:02:45 GMT

Thanks for your replay Gysbert!

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

Re: Day by day calculation

Thu, 13 Nov 2014 12:04:48 GMT

Hi,

try this

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

Re: Day by day calculation

Thu, 13 Nov 2014 12:12:02 GMT

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.

Re: Day by day calculation

ashwanin — Thu, 13 Nov 2014 12:16:49 GMT

Hi Hila,

Can you post some sample data.

Re: Re: Day by day calculation

Gysbert_Wassenaar — Thu, 13 Nov 2014 12:22:54 GMT

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

Re: Re: Day by day calculation

Thu, 13 Nov 2014 12:26:59 GMT

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.

Re: Re: Day by day calculation

Thu, 13 Nov 2014 12:56:25 GMT

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!

Re: Re: Day by day calculation

Gysbert_Wassenaar — Thu, 13 Nov 2014 16:39:16 GMT

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.

Re: Re: Day by day calculation

Thu, 13 Nov 2014 16:47:26 GMT

OK I think I understand what you mean and I will defiantly try it.

Thanks Gysbert!