topic Re: Setting Variables in QlikView

Setting Variables

danielnevitt — Thu, 01 Aug 2013 15:05:01 GMT

Hi,

I am trying to set variables to allow us to add expressions to straight tables to show the sum of current month, previous month etc.

My column name is CONTRACT_MONTH

The variables I have set are:

vCurrMonth = =Max(CONTRACT_MONTH)

vLastMonth = =vCurrMonth-1

The above return numeric results ie vCurrMonth = 8 and vLastMonth = 7

My CONTRACT_MONTH data is in the format Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. I am summing this data by a column called CONTRACT_QTY.

Would it be possible for someone to advise firstly how I set the variables to the above format and secondly how I can show those results in a straight table similar to the below:

Desk SumCurrMonth SumLastMonth

Any help would be much appreciated.

Regards,

Daniel

Re: Setting Variables

Gysbert_Wassenaar — Thu, 01 Aug 2013 16:44:14 GMT

If you have a field with month numbers you could use that instead. Or create one in the script and then use it. Or you could add two helper variables to get the month names from the numbers:

vCurrMonthName: =month(makedate(1,vCurrMonth))

vLastMonthName: =month(makedate(1,vLastMonth))

SumCurrMonth: =sum({<CONTRACT_MONTH={'$(vCurrMonthName)'}>}CONTRACT_QTY)

SumLastMonth: =sum({<CONTRACT_MONTH={'$(vLastMonthName)'}>}CONTRACT_QTY)

Re: Setting Variables

danielnevitt — Thu, 01 Aug 2013 17:01:26 GMT

Hi Gysbert,

Thank you for the reply.

The setting the MonthName works great.

Unfortunately the SumCurrMonth and SumLastMonth do not work.

The following expressions work (without variables):

sum({<CONTRACT_MONTH={`Jun`}>}CONTRACT_QTY)

sum({<CONTRACT_MONTH={`Jul`}>}CONTRACT_QTY)

Are you able to suggest how to insert the variables to the above?

Thanks

Daniel

Re: Setting Variables

Gysbert_Wassenaar — Thu, 01 Aug 2013 17:38:00 GMT

No idea. It works for me. See attached example.