topic Re: set analysis problem in QlikView

set analysis problem

dcd123456 — Thu, 30 Apr 2015 12:25:12 GMT

Hello

i have data from several days like this:

PER	fecha	user
ene	01/01/2015	u1
ene	01/01/2015	u2
ene	02/01/2015	u3
ene	02/01/2015	u4
ene	03/01/2015	u4
ene	04/01/2015	u5
ene	05/01/2015	u6
feb	01/02/2015	u1
feb	01/02/2015	u2
feb	02/02/2015	u3
feb	03/02/2015	u4
mar	01/03/2015	u1
mar	02/03/2015	u2

in want to display , in a bar chart, the count of diferent users agretate by fiel PER(month), but only take the data of the firsts day of each month, the number of days is selected by the user.

y tray to do this with set filter like this:

count({<fecha={"<=$(=date(min(fecha)+$(n_dias)))"}>}DISTINCT user)

n_dias is a variable that hold the number of days.

my problem is that min(fecha) always return one date, the global lowest date 01/0/01/2015, the chart is agregate by PER and i need that the min(fecha) return the min date of each month, not the lowest date.

i have attached an example in a file qvw .

thanks in advance for your help

regards

diego

Re: set analysis problem

Thu, 30 Apr 2015 13:09:06 GMT

you should have to create another table in the script. This new table will be linked with the fact tables (with user).

This new table should contain the user and his/her min date. You can do it easily just using resident of the fact table and a group by user.

Re: set analysis problem

dcd123456 — Thu, 30 Apr 2015 19:02:09 GMT

Thanks for your answer.

regads

diego

Re: set analysis problem

dcd123456 — Fri, 01 May 2015 07:32:48 GMT

Hello

in the script i make a new table that have a new field that is the min date the result is like this

PER	fecha	user	min_fecha
ene	01/01/2015	u1	01/01/2015
ene	01/01/2015	u2	01/01/2015
ene	02/01/2015	u3	01/01/2015
ene	02/01/2015	u4	01/01/2015
ene	03/01/2015	u4	01/01/2015
ene	04/01/2015	u5	01/01/2015
ene	05/01/2015	u6	01/01/2015
feb	01/02/2015	u1	01/02/2015
feb	01/02/2015	u2	01/02/2015
feb	02/02/2015	u3	01/02/2015
feb	03/02/2015	u4	01/02/2015
mar	01/03/2015	u1	01/03/2015
mar	02/03/2015	u2	01/03/2015

In the graph i use the next expresion:

count({1<fecha={"<=$(=date(min_fecha+$(n_dias)))"}>}DISTINCT user)

but the graph only show data if i select one value of PER field (a month), if i don't select any value of PER field the graph is empty. And if i select one value of PER field (month) the graph only show the correct value for the value selected, the other PER values show the global count and don't take in account the filter of the set expresion (<fecha={"<=$(=date(min_fecha+$(n_dias)))"}>)

best regards

diego

Re: set analysis problem

stigchel — Fri, 01 May 2015 08:52:08 GMT

The problem here is that the set (and $ sign expansion) are evaluated at the chart level and not for each dimension value. As there are several possible min dates for the whole dimension the expression will fail.

Try with

count(DISTINCT if(fecha<=date(min_fecha+$(n_dias)),user,null()))

Re: set analysis problem

maxgro — Fri, 01 May 2015 09:27:17 GMT

I think (if you want the first n_dias days of every month) you should add a day in your model and use that in set analysis

in script add

Day(fecha) as day

expression in chart

count({$<day={"<=$(n_dias)"}>}DISTINCT user)

Re: set analysis problem

dcd123456 — Fri, 01 May 2015 14:03:37 GMT

Thanks to all.

Regards

diego