topic Set analysis in QlikView

Set analysis

Tue, 23 Aug 2011 10:28:05 GMT

can anyone see what is wrong with this expression? Is it an issue with syntax? Or is it the fact that I cant embed set analysis into an if statement? Thanks

sum(if(analysis_a='11', {$<Date_MonthYear={">=$(#v3Mth) <=$(#v1Mth)"}>} val*(36.75/100), {$<Date_MonthYear={">=$(#v3Mth) <=$(#v1Mth)"}>} (val-(despatched_qty*cost_of_sale))))

Set analysis

SunilChauhan — Tue, 23 Aug 2011 10:55:55 GMT

sum(if(analysis_a='11', {$<Date_MonthYear={">=$(=#v3Mth) <=$(=#v1Mth)"}>} val*(36.75/100), {$<Date_MonthYear={">=$(=#v3Mth) <=$(=#v1Mth)"}>} (val-(despatched_qty*cost_of_sale))))

Re: Set analysis

Miguel_Angel_Baeyens — Tue, 23 Aug 2011 11:33:32 GMT

Hi Brian,

It's a combination of both. You can use set analysis in an aggregation function such as Sum(), and use it in conjunction with an If() conditional, with the proper syntax. Besides, make sure that Date_MonthYear has a numeric value, because is what you are forcing when you interpret $(#v3Mth) (the "#" means the result must be interpreted as numeric). So it should look like this

sum({$< Date_MonthYear = {">=$(#v3Mth)<=$(#v1Mth)"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set analysis

Tue, 23 Aug 2011 13:12:43 GMT

Another question please:

I put v3mth =date(addmonths(Date_MonthYear,-3),'MMM-YYYY') in my Variable Overview. This worked correctly.

However when i put it in my script, it did not work:

let v3mth = date(addmonths(Date_MonthYear,-12),'MMM-YYYY');

Any ideas why this happened? Thanks

Set analysis

Tue, 23 Aug 2011 15:27:24 GMT

Is there something wrong with this expression?

=date(addmonths(Date_MonthYear,-24),'MMM-YYYY')

Re: Set analysis

Miguel_Angel_Baeyens — Tue, 23 Aug 2011 15:28:10 GMT

Brian,

What's the content of the field Date_MonthYear in each case? AddMonths() need a full date field (year, month, day) to work, and "-12" should be accepted as it is -3 or any other valid numeric value, hence my guess that the issue may be in the format of the values in Date_MonthYear.

In any case, values returned by that Date() are not numeric, so the expression above using $(#v3Mth) is unlikely to work. Do they work?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Set analysis

Miguel_Angel_Baeyens — Tue, 23 Aug 2011 15:29:56 GMT

No, there's not.

Assuming Date_MonthYear has the value 23/08/2011 (format DD/MM/YYYY) your expression above should return "Aug-2009".

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set analysis

Tue, 23 Aug 2011 15:32:26 GMT

Thanks for the reply Miguel,

Date_MonthYear contains a correct date field.

The code does not work when I put it in the script, but it works correctly when I declare the variables in the Variable Overview.

Here is the code in the script:

Date as invoice_date,

date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,

let vDate12 = date(addmonths(Date_MonthYear,-12),'MMM-YYYY');

let vDate24 = date(addmonths(Date_MonthYear,-24),'MMM-YYYY');

let vDateNow = Date_MonthYear;

Re: Set analysis

Miguel_Angel_Baeyens — Tue, 23 Aug 2011 16:00:47 GMT

Brian,

In the script you need to pass on to the Date() functions only one value of all the possible in the field Date_MonthYear. According to your syntax, you are passing the field name, which will not work. Check the attached sample application on how to get that working in the load time.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set analysis

Tue, 23 Aug 2011 16:05:25 GMT

Thank you Miguel, I'll try that out

Set analysis

Wed, 24 Aug 2011 11:08:06 GMT

Miguel,

I took the variables out of my script and declared them in the variable overview. The reason for doing this was because I didnt want the date to be static. For instance if I checked May-2010 I wanted the varibles to reflect this date.

For example:

Date_MonthYear is in my table "DateParts" at the beginning of my script.

"Date as Date

date(monthstart(Date), 'MMM-YYYY') as Date_MonthYear"

vDateNow = Date_MonthYear

vDate12 = date(addmonths(Date_MonthYear, -12),'MMM-YYYY')

vDate24 = date(addmonths(Date_MonthYear, -24),'MMM-YYYY')

I have three textboxes on my sheet. The values they display are as follows:

Textbox1 - vDateNow: which gives me the value of the current selected month/year. For instance "May 2010"
Textbox2 - vDate12: which gives the value "May 2009"
Textbox3 - vDate24: which gives the value "May 2008"

In my pivot chart I have a number of expression, for example the following, which gives me the correct value.

count({$< Date_MonthYear = {">=$(vDate12)<=$(vDateNow)"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

however the current expressions are giving a '0' value.

count({$< Date_MonthYear = {">=$(vDate24)<=$(vDate12)"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

Is there a reason why the last expression will not work, even though the dates are being displayed with no problem on the textboxes?

Apologies for leaving such a long post, I hope I explained it clearly.

Re: Set analysis

Miguel_Angel_Baeyens — Wed, 24 Aug 2011 15:53:43 GMT

Brian,

According to your variable definitions, in all cases the result is a string. Strings can use the operators "follow" and "precede" is you want to make some text comparison, but this is not the case. So you need some additional transformation in either your expression or your script to get that right, something like

Count({$< Date_MonthYear = {"=Date(Date#(Date_MonthYear, 'MMM YYYY')) >= Date(Date#($(vDate24), 'MMM YYYY'))", "=Date(Date#(Date_MonthYear, 'MMM YYYY')) <= Date(Date#($(vDate12), 'MMM YYYY'))"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

So now you are comparing a number to a number, which is fine. That seems too messy though, doesn't it? I'd rather play with the AddMonths function and the Date field (the complete DD/MM/YYYY field), so why not use instead

Count({$< Date = {">=$(=AddYears(vDateNow, -2))<=$(=AddYears(vDateNow, -1))"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

And set vDateNow as a variable having a full date but represented as 'MMM YYYY', or even better, a Slider/Calendar object (calendar) where you select the month by clicking on any day of that month.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Set analysis

Fri, 26 Aug 2011 10:00:52 GMT

Thanks for the help Miguel!