Set Analysis Expression

[Giovane]

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.

[vinieme12 · Accepted solution]

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'

[PrashantSangle]

Hi,

try like

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

Regards

[vinieme12]

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 ??

[Anonymous]

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

[PrashantSangle]

Hi,

correct

Your solution will work

Regards

[Giovane]

@

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.

[vinieme12]

Hi Giovane,

Glad to help

Please close this thread

Qlik Community Tip: Marking Replies as Correct or Helpful

[Giovane]

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

[vinieme12]

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