topic Re: Use Set Analysis to sum records with all 13 Time Periods in QlikView

Use Set Analysis to sum records with all 13 Time Periods

Anonymous — Mon, 20 Feb 2017 16:20:59 GMT

Hello,

I'm having a hard time thinking of the formula to sum records that have 13 Periods by Site.

Here is an example image

As you can see, Site 4 only has 3 periods, where as Site 6 has 13 periods. I would like to be able to omit Site 4 entirely since it doesn't have the full 13 periods.

Here is my formula, but I believe it is summing all periods

sum({<gl_Flag={'1'} , Year={'2015'}, [BUS TYPE]={'RETAIL'},[FUEL $]={'>0'},PERIOD={'<=13'}>}[FRESCH GP$])

Any help would be appreciated!

Thanks,

Channing

Re: Use Set Analysis to sum records with all 13 Time Periods

aarkay29 — Mon, 20 Feb 2017 16:33:18 GMT

try this

sum({<gl_Flag={'1'} , Year={'2015'}, [BUS TYPE]={'RETAIL'},[FUEL $]={'>0'},PERIOD={'>=13'}>}[FRESCH GP$])

Re: Use Set Analysis to sum records with all 13 Time Periods

Anonymous — Mon, 20 Feb 2017 16:53:48 GMT

Will this only include records with 13 as the period?

Re: Use Set Analysis to sum records with all 13 Time Periods

Anonymous — Tue, 21 Feb 2017 19:29:44 GMT

bump

Re: Use Set Analysis to sum records with all 13 Time Periods

aarkay29 — Tue, 21 Feb 2017 19:45:10 GMT

Check if this works you!

sum({<gl_Flag={'1'} , Year={'2015'}, [BUS TYPE]={'RETAIL'},[FUEL $]={'>0'},Site={"=Count(Site)>=13">}[FRESCH GP$])

Re: Use Set Analysis to sum records with all 13 Time Periods

Anonymous — Tue, 21 Feb 2017 20:03:32 GMT

Perfect! Thank you!