topic Help in Set Expression in QlikView

Help in Set Expression

Anonymous — Mon, 17 Aug 2015 07:44:22 GMT

Hi All,

I need to calculate sum of values based on current selections.

My requirement is to calculate difference between Current month and Current month -1 .

My Date format is in YYYY.MM

(sum({<PERIOD_YEAR=,PERIOD={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)

sum({<PERIOD_YEAR={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross))

(sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM')))"},MONTHNAME=,MONTH_NAME=,{"CA"}>}Gross)

sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM')))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross))

I tried the above expression it displaying null values.

Thanks

Re: Help in Set Expression

sasiparupudi1 — Mon, 17 Aug 2015 07:53:40 GMT

(sum({<PERIOD_YEAR=,PERIOD={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)

sum({<PERIOD_YEAR={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross))

(sum({<PERIOD_YEAR=,PERIOD={"=$(=Date(addmonths(max(PERIOD),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)/

(sum({<PERIOD_YEAR=,PERIOD={"=$(=Date(addmonths(max(PERIOD),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)

Re: Help in Set Expression

jonathandienst — Mon, 17 Aug 2015 08:03:48 GMT

Create a test straight table with the same dimensions, and then use one expression for each part of the complete expression to check which parts work, and which parts don't. Then fix the parts that don't work and put the expression back together again.

sum({<PERIOD_YEAR=,PERIOD={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)
sum({<PERIOD_YEAR={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)
sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM')))"},MONTHNAME=,MONTH_NAME=,{"CA"}>}Gross)
sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM')))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)

You do appear to have too many closing parentheses in the last two:

... -

sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM'))"}, MONTHNAME=, MONTH_NAME=,{"CA"}>}Gross)

sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM'))"},MONTHNAME=, MONTH_NAME={"OS"}>}Gross)

Re: Help in Set Expression

Anonymous — Mon, 17 Aug 2015 09:18:43 GMT

I have problem with last two set expression.

I am getting values for the above two set expressions.

I have proble with thses two set expressions.

sum({<PERIOD_YEAR=,PERIOD={"=$(=Date(addmonths(max(PERIOD),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)

sum({<PERIOD_YEAR=,PERIOD={"=$(=Date(addmonths(max(PERIOD),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)

Thanks

Re: Help in Set Expression

sasiparupudi1 — Mon, 17 Aug 2015 09:34:02 GMT

try

(sum({<PERIOD_YEAR=,PERIOD={"$(=Date(addmonths(date#(max(PERIOD),'YYYY.MM'),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)/

(sum({<PERIOD_YEAR=,PERIOD={"$(=Date(addmonths(date#(max(PERIOD),'YYYY.MM'),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)

Re: Help in Set Expression

Alvaro_Palacios — Mon, 17 Aug 2015 09:59:32 GMT

Hi Rgv Rand,

You need to pass a date to the addmonths() function. So this should work out:

(sum({<PERIOD_YEAR=,PERIOD={"$(=Date(AddMonths(date(max(PERIOD)),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross))

(sum({<PERIOD_YEAR=,PERIOD={"$(=Date(AddMonths(date(max(PERIOD)),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross))

Regards,

Alvaro P.

Re: Help in Set Expression

Anonymous — Mon, 17 Aug 2015 10:18:04 GMT

Thank You