topic Re: Not working in QlikView

Not working

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

Hi all.

Can anyone see what is wrong with this Set Analysis? I am trying to get Sales of Last year's, in this case July 1st - July 26th but am getting unexplained value

SUM({<YR=, MO=, DA=, QTR=, MonthYear=, TranDate={">=$(=Date(MonthStart(today(),-12))) <=DATE(AddMonths(Today(),-12))"}>}OrderValue)

TranDate is M/D/YYYY (7/26/2016)

My calendar is selecting Year = 2016

Thanks

Re: Not working

vinieme12 — Tue, 26 Jul 2016 19:22:02 GMT

SUM({<YR=, MO=, DA=, QTR=, MonthYear=, TranDate={">=$(=Date(MonthStart(today(),-12)))

<=$(=DATE(AddMonths(Today(),-12)))"}>}OrderValue)

Re: Not working

trdandamudi — Tue, 26 Jul 2016 19:22:43 GMT

Give a try with below expression:

SUM({<YR=, MO=, DA=, QTR=, MonthYear=, TranDate={"$(= '>=' &Date(MonthStart(today(),-12))) & '<=' &DATE(AddMonths(Today(),-12))"}>} OrderValue)

Re: Not working

jduenyas — Tue, 26 Jul 2016 19:28:10 GMT

I thought so too. But when using the <=$(=DATE(AddMonths(Today(),-12)))"}>} portion the return value is $0.00

And that is incorrect

Re: Not working

vinieme12 — Tue, 26 Jul 2016 19:36:48 GMT

ok and this

SUM({<YR=, MO=, DA=, QTR=, MonthYear=,

$(=NUM([TranDate])) ={">=$(=NUM(MonthStart(today(),-12)))

<=$(=NUM(AddMonths(Today(),-12)))"}>}OrderValue)

Re: Not working

jduenyas — Tue, 26 Jul 2016 19:45:07 GMT

Same result as my original expression. ... unexplained value...

Re: Not working

jduenyas — Tue, 26 Jul 2016 19:45:31 GMT

I am trying to put a sample together.

Re: Not working

Peter_Cammaert — Tue, 26 Jul 2016 20:43:19 GMT

As a test, you can put the two $-sign expansions in a text box to see what they produce and whether this set analysis spec is able to select records. Like

='>=$(=Date(MonthStart(today(),-12)))' & chr(10) &

'<=$(=Date(AddMonths(Today(),-12)))'

Best,

Peter

[Edit] I used Vineeth's expression because I think the original one produces an invalid set modifier