topic Re: How to calculate first row/column after implementing after/before function? in QlikView

How to calculate first row/column after implementing after/before function?

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

Hi Community,

Do you have any idea on how to approach this? I would like to select a range of dates but the first column should still calculate the difference of the "working day" before.

I have a pivot pivot table with the following columns:

Week,

Date,

Accumalative Total,

Difference with day before.

The problem is, when I select week 26 for example, the column "Difference with day before" for the 25-06-2018 is always empty. This is because the the 24th hasn't been selected. Also when I select a range of date, 26-6-2018 to 27-06-2018, the column "Difference with day before" for the 26-06-2018 is empty with the same reason.

How can I best solve this?

I have tried to add the following formula =if(ColumnNo() = 1,'Y','T') but whenever i'm adding the calculations I get "-".

Thanks in advance!

Re: How to calculate first row/column after implementing after/before function?

raajaswin — Tue, 26 Jun 2018 07:42:18 GMT

First Expression you have used some if and range sum. when we select some day the result of your expression and the result of just count (distinct ) is same.

Re: How to calculate first row/column after implementing after/before function?

mrichman — Tue, 26 Jun 2018 07:44:51 GMT

Hi Aswin,

Thanks for your message. I don't understand what you mean. Could you please elaborate.

Thanks in advance.

Re: How to calculate first row/column after implementing after/before function?

raajaswin — Tue, 26 Jun 2018 07:51:31 GMT

I was confused earlier. Now understood your query trying on that

Re: How to calculate first row/column after implementing after/before function?

raajaswin — Tue, 26 Jun 2018 08:03:27 GMT

Try this in your second expression

=before(TOTAL rangesum([Accumalative Total],0,0))

Re: How to calculate first row/column after implementing after/before function?

mrichman — Tue, 26 Jun 2018 08:28:38 GMT

Hi Aswin,

Thanks for your prompt reply. It is still not working.

If you select week 26, then 25-06-2018 is empty. It should also calculate the difference between 25-06-2018 the working day before, in this case 24-06-2018.

Thanks in advance.

Re: How to calculate first row/column after implementing after/before function?

jyothish8807 — Tue, 26 Jun 2018 09:21:32 GMT

Hi Richman,

Can you share your expected output ? I am not able to understand your requirement.

Br,

Re: How to calculate first row/column after implementing after/before function?

sunny_talwar — Tue, 26 Jun 2018 11:31:44 GMT

Try this

If(Match(Status, 'Level 1', 'Level 2'),

Count(DISTINCT ClientNumber),

Count(TOTAL <Team, Date> DISTINCT ClientNumber) - RangeSum(Above(Count(DISTINCT ClientNumber), 1, RowNo())))

Before(TOTAL

If(Match(Only({1}Status), 'Level 1', 'Level 2'),

Count({<Date>} DISTINCT ClientNumber),

Count({<Date>}TOTAL <Team, Date> DISTINCT ClientNumber) - RangeSum(Above(Count({<Date>}DISTINCT ClientNumber), 1, RowNo()))))

Re: How to calculate first row/column after implementing after/before function?

mrichman — Tue, 26 Jun 2018 11:32:21 GMT

Hi Jyothish KC.

Thanks for your message. Here's the sample.

Re: How to calculate first row/column after implementing after/before function?

mrichman — Tue, 26 Jun 2018 13:27:30 GMT

Hi Sunny,

Thanks for the reply. Almost. The output is what I want, but now i'm unable to select the week or date in the pivot table.

Do you maybe have another solution?

Thanks in advance.

Re: How to calculate first row/column after implementing after/before function?

sunny_talwar — Tue, 26 Jun 2018 13:30:40 GMT

First, what do you mean you are unable to select Week or Date in the table? The screenshot above shows the Date selected... week is a calculated dimension made up of date field (Week(Date)) that is why the current selection shows the Date when I selected Week 26.

Second, the other option and rather a better one would be to use The As-Of Table

Re: How to calculate first row/column after implementing after/before function?

mrichman — Tue, 26 Jun 2018 13:46:48 GMT

Hi Sunny,

Thank you for your message. I Have attached an attachment regarding the error.

Furthermore, the As-Of-Table was already recommended by you last time. However, i'm not known on how to implement it. Even after trying.

Re: How to calculate first row/column after implementing after/before function?

sunny_talwar — Tue, 26 Jun 2018 13:50:44 GMT

Try this

(If(Match(Status, 'Level 1', 'Level 2'),

Count(DISTINCT ClientNumber),

Count(TOTAL <Team, Date> DISTINCT ClientNumber) - RangeSum(Above(Count(DISTINCT ClientNumber), 1, RowNo())))

Before(TOTAL

If(Match(Only({1}Status), 'Level 1', 'Level 2'),

Count({<Date>} DISTINCT ClientNumber),

Count({<Date>}TOTAL <Team, Date> DISTINCT ClientNumber) - RangeSum(Above(Count({<Date>}DISTINCT ClientNumber), 1, RowNo()))))) * Avg(1)

Re: How to calculate first row/column after implementing after/before function?

mrichman — Sat, 30 Jun 2018 06:16:30 GMT

Thanks for your help sunny! I had to change some things in the formula but I believe it's working properly now.