topic Re: Group by and Count functions in QlikView

Group by and Count functions

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

Well, that's the situation:

I have five columns - code, goal, month, year and percent - organized that way, for example:

Code Goal month year percent

1 Develop new solution 2 2016 85%

1 Introduce business model 10 2016 40%

The columns 'Month' and 'Year' refer to the delivery deadline. The 'Percent' column refer to the evolution of goal.

It is possible to compare 'Year' and 'Month' to the current date, perform a counting function and set up a chart that tell me what goals are delayed and which goals are OK?

(I want to do this in a chart expression).

I am very grateful for the help.

Re: Group by and Count functions

sunny_talwar — Tue, 29 Mar 2016 13:41:39 GMT

Not sure I fully understand the requirement. Would you be able to draw out what you are looking for in your chart?

Re: Group by and Count functions

Anonymous — Tue, 29 Mar 2016 13:50:02 GMT

Yes, that's my doubt. I Would like to create a chart with two divisions : Late goals and goals that are not delayed, comparing its deadline date to the current date. It's possible to create an expression in the chart to do this?

Re: Group by and Count functions

sunny_talwar — Tue, 29 Mar 2016 16:19:47 GMT

Based on the data provided, which goal is not delayed which one is?

Re: Group by and Count functions

Anonymous — Tue, 29 Mar 2016 17:21:37 GMT

Based on the data provided, the goal 'Develop New Solution' is delayed because it deadline date is February / 2016, and the goal 'Introduce Business Model' is not delayed because it deadline date is October / 2016.

The expression must use the current date as parameter. If a goal is scheduled for March and we are in April, this project / goal is delayed...

Re: Group by and Count functions

sunny_talwar — Tue, 29 Mar 2016 23:00:04 GMT

May be like this?

Script:

Table:

LOAD Code,

Goal,

Month(Date) as Month,

Year(Date) as Year,

MonthName(Date) as MonthYear,

Date,

percent as Percent;

LOAD *,

Date(MonthStart(Date#(Num(month&year, '000000'), 'MMYYYY')), 'MMM-YYYY') as Date;

LOAD * Inline [

Code, Goal, month, year, percent

1, Develop new solution, 2, 2016, 85%

1, Introduce business model, 10, 2016, 40%

];

Dimensions:

=If(MonthYear <= MonthName(Today()), 'Delayed', 'Not Delayed')

Expression:

Count(Goal)

Re: Group by and Count functions

Anonymous — Wed, 30 Mar 2016 11:54:40 GMT

Thank you very much!