topic 'and' in set analysis in QlikView

'and' in set analysis

hansrokers — Mon, 25 Mar 2013 08:36:44 GMT

Hello,

I am strugling with the following:

I need need the sales of last year, lower then the max week and lower then the current week.

Something like:

sum({$ <Year={$(=max(Year)-1)},Week = {"<$(=week(Today()))"} and Week = {"<$(=max(Week))"} >} Sales )

but this does not work.

Any suggestions?

Re: 'and' in set analysis

alexandros17 — Mon, 25 Mar 2013 08:39:02 GMT

Try this, "and" is replaced by comma ....

sum({$ <Year={$(=max(Year)-1)},Week = {"<$(=week(Today()))","<$(=max(Week))"} >} Sales )

hope it helps

Re: 'and' in set analysis

hansrokers — Mon, 25 Mar 2013 08:59:30 GMT

This wil give me the total sales of last year as no week has been selected

Re: 'and' in set analysis

kaushiknsolanki — Mon, 25 Mar 2013 09:06:05 GMT

Try this,

sum({$ <Year={$(=max(Year)-1)},Week = {"<$(=week(Today()))<$(=max(Week))"} >} Sales )

Regards,

Kaushik Solanki

Re: 'and' in set analysis

alexandros17 — Mon, 25 Mar 2013 09:06:35 GMT

At this point try to use a workaround (I don't know how your data are organized):

With a variable compute max between max(week) and week(today()) then use that only variable in set analysis

Re: 'and' in set analysis

hansrokers — Mon, 25 Mar 2013 09:13:24 GMT

This results in 0.

Re: 'and' in set analysis

kaushiknsolanki — Mon, 25 Mar 2013 09:17:24 GMT

Hi,

Its because you may be be having the relevant data.

Kindly upload your QVW file.

Regards,

Kaushik Solanki

Re: 'and' in set analysis

hansrokers — Mon, 25 Mar 2013 09:29:03 GMT

I have been able to solve the problem with an if-statement, but I am sure there must be a better solution:

if(GetSelectedCount(Week)>0, sum({$ <Year={$(=max(Year)-1)},Week = {"<=$(=max(Week))"} >} Sales),sum({$ <Year={$(=max(Year)-1)},Week = {"<=$(=week(today()))"} >} Sales))

Re: 'and' in set analysis

kaushiknsolanki — Mon, 25 Mar 2013 09:34:45 GMT

Hi,

You can replace this using below expression.

sum({$ <Year={$(=max(Year)-1)},Week = {"<=$(=if(GetSelectedCount(Week)>0,max(Week),week(today())))"} >} Sales)

Regards,

Kaushik Solanki

Re: 'and' in set analysis

jagan — Mon, 25 Mar 2013 09:56:29 GMT

Hi,

Try this

=sum({$ <Year={$(=max(Year)-1)}, Week = {"<$(=week(Today()))"} * {"<$(=max(Week))"} >} Sales )

Hope this helps you.

Regards,

Jagan.