topic issue in expression in QlikView

issue in expression

Anonymous — Wed, 18 Jan 2017 05:54:57 GMT

Dear Team

I have using below expression to get last 6 months sales. It is not calculating for the last 6 months but only returns sales for the month selected. Kindly help if there is something to be modified.

=SUM ( {< Monyr = {">=$(vSTART)<$(vEND)"} >} Sales)

where variables are defined as

vSTART= date(MonthStart(Date(max(MonthYear),'M YYYY'),-6),'M YYYY')

vEND= = num(MONTH(MonthYear),'0') &' ' & YEAR(MonthYear)

The field Monyr is available as 1 2016, 2 2016, 3 2016,......... 10 2016 ..

Thanks & Regards

Chintan

Re: issue in expression

jonathandienst — Wed, 18 Jan 2017 06:10:35 GMT

Is MonthYear a field or a variable?

Is it text, or is it a numeric date field?

If text, how is it formatted?

Monyr is presumably a text field. This set expression will require a numeric date value or a different format if the field is text, or the comparisons will not work,

Re: issue in expression

Anonymous — Wed, 18 Jan 2017 06:17:41 GMT

Dear Jonathan

Thanks for your reply. Monyr is a field and made in script as below:

num(month(Date))& ' '&Year(Date) as Monyr,

data is stored as 1 2016, 2 2016, 3 2016.............10 2016 etc.

Is it that I need to use num even in vSTART variable?

Thanks & regards

Chintan

Re: issue in expression

jonathandienst — Thu, 19 Jan 2017 09:54:13 GMT

You will need to convert Monyr to a numeric date field if you want to do this sort analysis - text compares with that format will not work. In the load, use this to define Monyr:

Date(Floor(Date), 'M yyyy') as Monyr,

Then the variables (also in the load):

Set vSTART= =Date(MonthStart(Max(MonthYear)),-6),'M YYYY');

Set vEND= = Date(Floor(MonthYear), 'M yyyy');

// Note the double = signs

Finally, your set expression

=SUM ({<Monyr = {">=$(vSTART) <$(vEND)"}>} Sales)

Re: issue in expression

Anonymous — Thu, 19 Jan 2017 10:15:15 GMT

Thanks for your reply. I will try and let you know if I get the desired result.

Regards

Chintan