topic Re: problem with set analysis with string variable in QlikView

problem with set analysis with string variable

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Good afternoon,

In the enclosed sample I tried without success to show the collected cash of a game for the same month but a year back of the year that I have currently selected.

my script is

LOAD datecollect,

game,

Money([cash],'€ #.##0') as cash,

MonthName(datecollect) as MonthYear,

Month(datecollect) as Month,

Year(datecollect) as Year

FROM

(ooxml, embedded labels);

//set vmyMonthpreviousYear = "(Month & ' ' & (Year-1))";

set vmyMonthpreviousYear = MonthName(AddMonths(datecollect, -12))

I can correctly show in a text box the value of the variable $(vmyMonthpreviousYear) which is the same month as the selected one and but one year back of the year that I have currently selected.

I can't find the correct set analysis in order to show the cash using the variable, while I can show the cash of a year using

=Sum({$<Year={'2016'},Month=>}cash)

maybe I am trying to use the wrong variable or I have taken it from the wrong way ? or it is a matter of syntax?

I thought that

=Sum({$<MonthYear={'$(vmyMonthpreviousYear)'}>}cash)

would work but it does not...

Your help is appreciated

Regards

Antonio

Re: problem with set analysis with string variable

sunny_talwar — Tue, 10 May 2016 17:31:29 GMT

Try this:

=Sum({$<MonthYear={'$(=$(vmyMonthpreviousYear))'}, Year, Month>}cash)

Re: problem with set analysis with string variable

Anonymous — Wed, 11 May 2016 09:40:38 GMT

Hi Sunny,

Thanks!

So, in the set analysis the dimension MonthYear, created during the script load, needs the dimension Year when using the variable:

=Sum({$<MonthYear={'$(=$(vmyMonthpreviousYear))'},Year>}cash)

Regards

Antonio

P.S.: I checked and just using Year does the job, do not need Month.

In my live envirenment I had to remove the dimension MonthYear from the straight table.