topic Re: Including unselected data in before-function in QlikView

Including unselected data in before-function

Fri, 25 Apr 2014 08:10:18 GMT

I've got a pivot-table with, among others, the below expression. The dimensions are country and month.

=before(TOTAL count({$<TaskStatus={'New'}>}TaskStatus))-before(TOTAL count({$<TaskStatus={'Cancelled'}>}TaskStatus))

This counts the difference between New and Cancelled tasks from last month. The formula works fine except if I make a date selection, then the first month in the selection becomes blank as there is no data for the before()-function. I can force an inclusion of the data (Month=) in the set analysis, but then the table displays all months.

So I want to hide the unselected values in the month-field, but still include them in the calculation. How do I do this? All other filters should still affect the calculation.

An example, the table without selections looks like this:

Dec 2013	Jan 2014	Feb 2014
2	5	4

So when selecting 2014 the table should look like this:

Jan 2014	Feb 2014
5	4

With my current formula it looks like this when selecting 2014 (wrong):

Jan 2014	Feb 2014
-	4

Re: Including unselected data in before-function

Tue, 29 Apr 2014 09:47:30 GMT

Anyone who can help me with this? I've been reading this article: Calculating rolling n-period totals, averages or other aggregations among others, but can't manage to solve it.

Re: Re: Including unselected data in before-function

tresB — Fri, 23 May 2014 09:34:16 GMT

Aggr() can be helpful. See the attached sample.

Re: Re: Including unselected data in before-function

Fri, 23 May 2014 10:42:27 GMT

Thank you tresesco for your help! Your example is working fine, but when I try to incorporate it in my expression I fail. Are you able to say how it should look?

This is my current expression:

=before(TOTAL count({$<TaskStatus={'New'}>}TaskStatus))

I tried to edit it like this, but that didn't help:

=Before(Only({1}Aggr(Count({$<TaskStatus={'New'}>}TaskStatus),Month)))

Re: Including unselected data in before-function

SunilChauhan — Fri, 23 May 2014 10:50:18 GMT

Month= in set analysis means to exclude only selection not to exclude data i.e Month

Re: Re: Including unselected data in before-function

tresB — Fri, 23 May 2014 10:54:49 GMT

Could post your sample qvw?

Re: Including unselected data in before-function

Fri, 23 May 2014 10:59:11 GMT

Unfortunately that is difficult as it is rather large with data I can't share. But the expression I'm using now is:

=before(TOTAL count({$<TaskStatus={'New'}>}TaskStatus))

This is in a pivot table where the vertical dimensions are Year and Month, horizontal dimension is Country.

Re: Including unselected data in before-function

Fri, 23 May 2014 10:59:40 GMT

Thanks but I don't quite understand what you mean. How do I need to change the expression then?

Re: Re: Including unselected data in before-function

tresB — Fri, 23 May 2014 11:04:03 GMT

See this: How to upload sample qvw

By the time try:

=Before(Only({1}Aggr(Count({$<TaskStatus={'New'}>}TaskStatus),Year,Month)))

Re: Re: Re: Including unselected data in before-function

Fri, 23 May 2014 11:34:17 GMT

Hi,

I had no idea about the scramble functionality, thank you!

Here is the file attached. I'm working on the second table ("Calculated data"), specifically the expression called "Target". In this file I'm using week instead of month as dimension.

The expression you proposed didn't work.

Re: Re: Re: Re: Including unselected data in before-function

tresB — Tue, 27 May 2014 08:07:35 GMT

Gustav,

I have been trying with your attached sample and it has been couple of hours in pieces since that day. Finally I found it that the Before() is not actually behaving properly with Aggr(), while it's counter-part Above() does. I am attaching a sample qvw that shows the issue. The straight table works fine but the pivot. It's strange for me.

Re: Re: Re: Re: Including unselected data in before-function

Tue, 27 May 2014 10:35:53 GMT

Thank you a lot tresesco for trying. I guess I will just need to include a textbox in my report, notifying people the first period in the selection will always be blank.

Re: Re: Re: Re: Re: Including unselected data in before-function

tresB — Tue, 27 May 2014 10:43:13 GMT

No, wait ! Got an alternative. Use fields TaskBu,Year,Week as dimensions, expression:

=Aggr((Above(TOTAL count({$<ReportStatus={'New'}>}ReportStatus))-Above(TOTAL count({$<ReportStatus={'Cancelled'}>}ReportStatus))),TaskBU,Year,Week)

Re: Re: Re: Re: Re: Including unselected data in before-function

Tue, 27 May 2014 12:02:26 GMT

Thank you tresesco, this looks like great progress! I changed references from Year to WeekYear. However, it still doesn't work (although now it displays something as opposed to nothing):

If you select years 2013 & 2014 then the first week of 2014 is correct.

If you select year 2014 then the first week of 2014 is incorrect (a much bigger figure).

Re: Re: Re: Re: Re: Including unselected data in before-function

tresB — Tue, 27 May 2014 12:06:04 GMT

When you chnaged the dimension, hope you made the similar change in the expression like:

=Aggr((Above(TOTAL count({$<ReportStatus={'New'}>}ReportStatus))-Above(TOTAL count({$<ReportStatus={'Cancelled'}>}ReportStatus))),TaskBU,Year,WeekYear)

And, if this doesn't take you to the goal, it has to be something with your fields when getting calculated at the backend.

Re: Re: Re: Re: Re: Including unselected data in before-function

Tue, 27 May 2014 12:10:32 GMT

Yes I did, I also tried setting only TaskBU and WeekYear as dimensions, and "=Aggr((Above(TOTAL count({$<ReportStatus={'New'}>}ReportStatus))-Above(TOTAL count({$<ReportStatus={'Cancelled'}>}ReportStatus))),TaskBU,WeekYear)" as expression.

But even so, you can see for yourself that the values for week 1 year 2014 in the target-column changes depending if you have 2014 selected or 2013 & 2014.