topic Re: set analysis not working in QlikView

set analysis not working

cafcptg2011 — Sat, 24 Mar 2012 15:37:50 GMT

Hi,

I have a set analysis problem that i can't understand why is not working. to better understand I attached a qvw file (Sales.qvw) to this thread.

Can anyone help me with this problem?

Thanks in advance,

set analysis not working

Mon, 26 Mar 2012 05:44:44 GMT

Hi there,

seen your sales qvw the result that you are getting on chart Sales after Request is same as waht it contains

in your loded tables , i beleve you have no problem with Request which you have done as sum(Request)

which is showing correct value as per your loaded data and in last coloumn you are using a set analysis,

to find sales quantity for each product on date wise the result which you are getting is as per your loaded

data how can you expect values like 10, 9, 6 in last coloumn when these quantites are not recorded on

these dates, also instead of set analysis expression you could use sum(SalesQty) , ibelevie you must check

the values pesent in data model , refresh , reload the data from source such as excel , use list boxes

select product a select date and check the results are coming as per your expextactions.

Warm regards,

Anant Dubey

set analysis not working

cafcptg2011 — Mon, 26 Mar 2012 09:01:56 GMT

Hi,

thanks for your reply. I will try explain better the problem. I want to know what were sales from the request dates. Basicly, I need to get Dates that are in RequesDate Column as I show in the picture below. If I put the set analysis express outside the table, for instance , in a text-object it works perfectly. Why is not working inside the table?? I realy need this, I don't know what to do more... 🙂

Thanks again.

Re: set analysis not working

Mon, 26 Mar 2012 11:38:46 GMT

Hi cafcPTG2011

Try this solution:

=sum({$<SalesDate={">=$(=date#('2012/03/06','YYYY/MM/DD')+0)"}>}SalesQty)

Here you made 2 mistakes

1. Date() is just used for switch display format, instead of the value. Date#() is the one you need to use. Actually not only Date(), but also Num().

Here is an example for you to understand:

create a straight table, define 1st expression as =Num(1/3,'#,##0.##'), 2 expression as =column(1) * 10

take a look what happen, column 1 value is 0.33, column 2 value is 3.333333333, instead of 3.3

That means, the value stored in memory is still the 0.3333333, it just change the display value as 0.33

Same thing happen for Date().

2.The 2nd thing is very strange, I feel this is a defect of QV, at least it should be an enhancement request. In most of database, the date is recognized as an integer, even qlikview. In most of user case, qlikview can use date variable as both date and integer.

For examples, after add those expression,both these 2 are work. And then take a look for column header:

use as a date variable:

=sum({$<SalesDate={">=$(=now())"}>}SalesQty)

as integer (date is integer, 1day=1; the float value is for hour/min/second):

=sum({$<SalesDate={">=$(=now()+0)"}>}SalesQty)

So, now() is working, let's try to replace it with date#(), oh, why it doesn't work?

=sum({$<SalesDate={">=$(=date#('2012/03/06','YYYY/MM/DD'))"}>}SalesQty)

Try + 0, it is workable now.

=sum({$<SalesDate={">=$(=date#('2012/03/06','YYYY/MM/DD')+0)"}>}SalesQty)

So, what happen? It just convert this value from date string to in integer.

I also feel confused with this result, I don't think a date variable can be a "date string", it must be int or date. So I think this is a bug of QlikView

BTW, my QV version is 11. I tried this solution on my environment, if not working, pls check if your version is same.

Re: set analysis not working

Mon, 26 Mar 2012 12:14:34 GMT

Eeeeee, I met a strange problem after resolve this issue.

Here your condition is ">=", but how to make "=" work?

For integer value, for example, year, it is very easy:

=sum({<Year={2011,2012}>} SalesQty)

But I tried this for SalesDate, not working. Don't know why.

I also tried +0, even directly put a integer value there, doesn't work neither.

set analysis not working

cafcptg2011 — Mon, 26 Mar 2012 13:59:02 GMT

Hi,

I tried your solution but still doesn't work. please see my picture.

thanks

ps: My QV version : 10.00 SR4 (x64)

Re: set analysis not working

Tue, 27 Mar 2012 09:31:59 GMT

Please refer to my previous context, your first mistake.

You used date(), instead of date#(), that's the reason why it doesn't work.

I have tried this function in my environment, it works fine.

=sum({$<SalesDate={">=$(=date#('2012/03/06','YYYY/MM/DD')+0)"}>}SalesQty)

Re: set analysis not working

cafcptg2011 — Tue, 27 Mar 2012 10:10:28 GMT

Hi,

your expression :

=sum({$<SalesDate={">=$(=date#('2012/03/06','YYYY/MM/DD')+0)"}>}SalesQty)

works correct, but when I replace '2012/03/06' for max(RequestDate) and I put it inside a straight-table

and then the expression stays like :

=sum({$<SalesDate={">=$(=date#(max(RequestDate),'YYYY/MM/DD')+0)"}>}SalesQty)

it just doesn't work....