topic Re: How to calculate previous Date 's sum(Sales)? in QlikView

How to calculate previous Date 's sum(Sales)?

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am using the below table:

[Data]:
load * inline [
DateData, Sales
12/11/2017, 2012
12/10/2017, 2015
12/09/2017, 2016
12/08/2017, 2017
12/07/2017, 2018
];

and use a straight table:

and use DateData as dimension and Sum({$<[Process Date] = {"$(date(only([Process Date]-1)))"}>}[Sales Posting Amount USD]) as expression. But I get '0' for all fields.

Please help me what is wrong in the expression?

Thanks,

Sandip

Re: How to calculate previous Date 's sum(Sales)?

sunny_talwar — Thu, 27 Apr 2017 11:45:36 GMT

May be try

Above(Sum({<DateData>}Sales)) * Avg(1)

or use The As-Of Table

Re: How to calculate previous Date 's sum(Sales)?

sunny_talwar — Thu, 27 Apr 2017 11:47:59 GMT

Here is a sample attached

Re: How to calculate previous Date 's sum(Sales)?

ahaahaaha — Thu, 27 Apr 2017 11:48:49 GMT

Hi,

May be expression Above(Sum (Sales))?

Re: How to calculate previous Date 's sum(Sales)?

sunny_talwar — Thu, 27 Apr 2017 11:50:55 GMT

AsOfTable example attached

Re: How to calculate previous Date 's sum(Sales)?

ananyaghosh — Thu, 27 Apr 2017 12:44:08 GMT

Hi,

I just attached my sample qvw file. Please can you edit the 'Demo' straight table chart. Currently it does not show any data for

'Prior Date Sale' column's value under it.

Thanks,

Sandip

Re: How to calculate previous Date 's sum(Sales)?

sunny_talwar — Thu, 27 Apr 2017 12:52:39 GMT

Since you used two dimension, you needed to add Total after Above

Above(TOTAL Sum({<[Process Date]>}[Sales Posting Amount USD])) * Avg(1)

Re: How to calculate previous Date 's sum(Sales)?

ananyaghosh — Thu, 27 Apr 2017 13:20:05 GMT

Hi,

it looks ok. But I need to implement the logic as : if the previous day's value is null or not exists in data model, then it will be null, but you can see that for Prior Date: 12/07/2016 , it is not present in the data model, but the prior date sale value is coming for 12/08/2017. SO it should be null or blank value. So instead of showing -100 it should show only 0 in the table.

So the logic should be if a date's prior date is not present in the data, it will show null or 0 prior date sale's column value.

Thanks,

Sandip

Re: How to calculate previous Date 's sum(Sales)?

sunny_talwar — Thu, 27 Apr 2017 13:21:23 GMT

Then it would be best to use The As-Of Table approach here

Re: How to calculate previous Date 's sum(Sales)?

ananyaghosh — Thu, 27 Apr 2017 13:24:46 GMT

Hi,

I have seen it. But I did not understand how to implement it in my case.

So I am requesting you if you can implement it in my sample QVW document.

Thanks,

Sandip

Re: How to calculate previous Date 's sum(Sales)?

sunny_talwar — Thu, 27 Apr 2017 13:50:54 GMT

I don't have the source files to run... but will try to do a binary load to try to make it work...

Re: How to calculate previous Date 's sum(Sales)?

sunny_talwar — Thu, 27 Apr 2017 14:08:38 GMT

Check if this looks good

Re: How to calculate previous Date 's sum(Sales)?

ananyaghosh — Thu, 27 Apr 2017 14:28:26 GMT

Hi,

Thanks and Thanks a Lot.

But in your as of calendar I need three more fields like:

previous month, previous week and previous year and last 9 and 6 months flag.

So can u give me that script also. because how can I make difference between weeks and months and year also.

So in this case if you help me I will be very thankful to you.

Please help me for previous month, previous week and previous year and last 9 and 6 months calculations.

I am just waiting for your reply.

Thanks,

Sandip

Re: How to calculate previous Date 's sum(Sales)?

sunny_talwar — Thu, 27 Apr 2017 14:34:49 GMT

Have you even looked at the script I have provided and the link I have shared? Can you try to give this a try on your end and ask for help if it doesn't help... or if someone else have time, they will help you out....

Best of luck brother....

P.S. As a recommendation, try to provide your requirement in one go (if possible).... I am sure once someone figures this out for you... you must have a list of other things you would like them to add