Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

set analysis: sum of amount when date in range not working

Hi Guys,

I'm trying to use set analysis and get the sum of a field called "count_recommendations" when its date is in range.

I'm using the following:

sum({<DateOnly= {'<=$(=vYesterday) >=$(=v3days)'}> } count_recommendation)

but the results show only 0.

vYesterday and v3days are variables that are set to recieve yesterday and 3 days ago dates respectively.

Am I using the wrong syntax? because an other question here with that topic was resolved with that answer....

can you help?

1 Solution

Accepted Solutions
Not applicable

Re: set analysis: sum of amount when date in range not working

FYI,

When you define the variables in Variables overview you have to use =

Eg.

vYes

=Today()-1

14 Replies
sorrakis01
Valued Contributor

Re: set analysis: sum of amount when date in range not working

Hi,

Try this:

Sum({$<[DateOnly] = {'$(vYesterday), $(v3days)'}>} count_recommendation)

if doesn't work

Maybe try this:

Sum({$<DateOnly = {$(vYesterday), $(v3days)}>} count_recommendation

you can try this with the values of the variable Sum({$<DateOnly = {'value1','value2')}>} count_recommendation

Cy

Not applicable

Re: set analysis: sum of amount when date in range not working

Hey Elula,

Can you put the two variables in a text box and see if you are getting expected results. You can try "  instead of ' .

Re: set analysis: sum of amount when date in range not working

Hi,

try double Quotes(") insted of single quotes(') this.

Regards,
Prashant Sangle
Not applicable

Re: set analysis: sum of amount when date in range not working

Doesn't work :\

Now it shows "-" instead of 0.

Not applicable

Re: set analysis: sum of amount when date in range not working

Not working...

I still get 0 when using " instead of ' ...

Re: set analysis: sum of amount when date in range not working

What is vYesterday and v3days? i.e. what output they are giving.

What is the formatting of DateOnly?


Not applicable

Re: set analysis: sum of amount when date in range not working

I have attached an example with exactly what you are trying to do. First take a look at the variables then the expression

Hope it helps.

Thanks

AJ

Not applicable

Re: set analysis: sum of amount when date in range not working

Hi,

try to set the number format of variables as date in the document properties in the tab variables.

Not applicable

Re: set analysis: sum of amount when date in range not working

they are showing the date as a number...

when I put them in list box I see:

vYesterday=41696

v3days= 41694

when using the expression in an other list box:

=if (DateOnly>=$(v3days),

if (DateOnly<=$(vYesterday), DateOnly))

I get: 41694, 41695, 49696

So you see they are all of the same format...

Community Browser