topic Re: Set Analysis Expression in QlikView

Set Analysis Expression

Giovane — Mon, 19 Dec 2016 11:38:03 GMT

Dear fellows,

I have 4 variables: vStartMonh, vStartYear, vEndMonth, vEndYear. I need to built a set analysis expression to sum sales from vStarMonth and vStartYear, to vEndMonth and vEndYear.

How can I do that? Can someone built this expression to me?

Best regards.

Re: Set Analysis Expression

vinieme12 — Mon, 19 Dec 2016 11:45:26 GMT

create a YearMonth field in your data table to simplify the calculations

example: 201601,201602 and so on

and vStartYearMonth = NUM(vStartYear & vStartMonth)

and vEndYearMonth = NUM(vEndYear & vEndMonth)

You expression should then be

SUM ( {< YearMonth = {">=$(vStartYearMonth)<=$(vEndYearMonth)"} >} Sales)

Just ensure your Month variables are '00' format meaning Jan is '01' and not '1'

Re: Set Analysis Expression

PrashantSangle — Mon, 19 Dec 2016 11:49:42 GMT

Hi,

try like

SUM ( {< MonthField = {">=$(vStartMonth)<=$(vEndMonth)"},YearField= {">=$(vStartYear)<=$(vEndYear)"} >} Sales)

Regards

Re: Set Analysis Expression

vinieme12 — Mon, 19 Dec 2016 11:52:55 GMT

this won't work when you want to calculate between period over different year

example between 2015 6 and 2016 3

Year >=2015<=2016

Month >=6<=3 ??

Re: Set Analysis Expression

Anonymous — Mon, 19 Dec 2016 11:55:37 GMT

Hi Giovane,

You can try sum({<Data_reportare={">=$(=vStarMonth) >=$(=vStartYear) <=$(=vEndMonth) <=$(=vEndYear)"}>})

If this doesn't work, try to define the variables with makedate(year(vStarMonth), month(vStarMonth),day(vStarMonth)).

Best regards,

Cosmina

Re: Set Analysis Expression

PrashantSangle — Mon, 19 Dec 2016 12:33:58 GMT

Hi,

correct

Your solution will work

Regards

Re: Set Analysis Expression

Giovane — Mon, 19 Dec 2016 17:57:21 GMT

Dear Vineeth,

You did the first tip to solve my issue. Tested and approved. It works.

Thanks a lot.

Thanks to all that contributed to help me.

Re: Set Analysis Expression

vinieme12 — Mon, 19 Dec 2016 18:52:46 GMT

Hi Giovane,

Glad to help

Please close this thread

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Set Analysis Expression

Giovane — Tue, 20 Dec 2016 10:33:14 GMT

Hi Vineeth.

I want hardly to close this thread, but even looking for how to do that, I don't know how...Can you help one more time?

Thanks

Re: Set Analysis Expression

vinieme12 — Thu, 22 Dec 2016 05:11:03 GMT

Marking a response as correct closes the thread.

Under each response you will see a green button, just click on it to mark a response as correct. That will close the thread

Re: Set Analysis Expression

Giovane — Tue, 17 Jan 2017 16:10:32 GMT

Thanks, Vineeth. Done. The thread is closed.

See you !!!