'and' in set analysis

[hansrokers]

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?

[alexandros17]

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

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

hope it helps

[hansrokers]

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

[kaushiknsolanki]

Try this,

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

Regards,

Kaushik Solanki

[alexandros17]

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

[hansrokers]

This results in 0.

[kaushiknsolanki]

Hi,

     Its because you may be be having the relevant data.

     Kindly upload your QVW file.

Regards,

Kaushik Solanki

[hansrokers]

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))

[kaushiknsolanki]

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

[jagan · Accepted solution]

Hi,

Try this

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

Hope this helps you.

Regards,

Jagan.