topic Re: Set Analysis aggregate with date range in QlikView

Set Analysis aggregate with date range

pirtekmismanage — Fri, 08 Jun 2018 10:40:37 GMT

Hi community,

I have a set analysis which works fine until I have got one company group selected. If I select more company groups or clear the filter it doesn't show any figures. Just a bit explanation for the data set. Every group has got different start and end date it is like a membership expiration date. I would like to get the sales within their time of membership status. When selecting a month it nicely accumulate month by month and when it reaches the end date it will not show accumulation further only the sales up to the end date. As I said it works perfectly until I have got one company group selected. Any idea what is missing?

SUM(AGGR(SUM({<Event = {'Sales'}, GroupAccountType = {'National'}, New_Renewal={'Re-opened','New'}, DateKeyNum = {">=$(=StartDateNum)<=$(=EndDateNum)"}>} TotalExcVAT),Zone,GroupName,MonthYear))

Thanks in advance for the help.

Re: Set Analysis aggregate with date range

sunny_talwar — Fri, 08 Jun 2018 15:35:52 GMT

Since the start and end date differ, you won't really be able to use set analysis... you might need to use if statement with aggr function. In case you need help with a specific expression, would you be able to share a sample to help you better

Re: Set Analysis aggregate with date range

ogautier62 — Sat, 09 Jun 2018 06:16:13 GMT

Hi,

what's the result ? a null value ?

if it's the case surely because sseveral aggr( ) give same result,

try add nodistinct after aggr and before sum

regards

Re: Set Analysis aggregate with date range

vikramv — Sat, 09 Jun 2018 19:49:58 GMT

you can try including the "company group" as one of the dimenions in the existing chart

If you have multiple Group Names with the varying dates then definitely you have to include the "company group" as a dimension in case you need to compare the data for themusing your current set expression.

====

You can cross check your data by creating a straight table by

Adding these expressions

SUM({<Event = {'Sales'}, GroupAccountType = {'National'}, New_Renewal={'Re-opened','New'}, DateKeyNum = {">=$(=StartDateNum)<=$(=EndDateNum)"}>} TotalExcVAT)

Maxstring(Date(Date#(StartDateNum))) ,

Maxstring(Date(Date#(EndDateNum)))

and check the result in a straight table to oserve what is causing your issue.

With Dimensions

Event , GroupAccountType , New_Renewal, ,Zone,GroupName, MonthYear

====

Re: Set Analysis aggregate with date range

pirtekmismanage — Tue, 12 Jun 2018 10:12:11 GMT

Hi Olivier,

The values are zeros as it shows below. If I select one of the companies from the straight table chart then gets the real figures. And with selection.

Re: Set Analysis aggregate with date range

pirtekmismanage — Tue, 12 Jun 2018 10:14:22 GMT

Hi Vikram,

I tested it and I still cannot see the issue. Max functions probably won't work as every company has got their separate start and end. With max I will pick the highest of all. I do not want that.

Re: Set Analysis aggregate with date range

pirtekmismanage — Tue, 12 Jun 2018 10:17:17 GMT

Hi Sunny,

Sorry for the delayed reply I got busy. I am not sure how I can upload a sample dashboard. I will try to figure out and update the topic shortly. Thanks in advance for your help.

Re: Set Analysis aggregate with date range

pirtekmismanage — Tue, 12 Jun 2018 10:25:25 GMT

Sunny,

I found help in one of your previous post "Upload Sample file" lol. You already helped!

Please see the sample file attached.

Re: Set Analysis aggregate with date range

sunny_talwar — Tue, 12 Jun 2018 11:22:23 GMT

Try this

Sum(Aggr(If(DateKeyNum >= StartDateNum and DateKeyNum <= EndDateNum,

Sum({<Event = {'Sales'}, GroupAccountType = {'National'}, New_Renewal={'Re-opened','New'}>} TotalExcVAT)),Zone, GroupName, MonthYear, DateKeyNum))

Re: Set Analysis aggregate with date range

pirtekmismanage — Tue, 12 Jun 2018 14:56:18 GMT

Hi Sunny,

It marked. It works perfectly. I have tried before with IF but I used outside of the AGGR function so it hasn't worked for me. Can I have some explanation what is the difference withing the AGGR or outside of the aggregate? Is it basically part of the set analysis in this case or still not?

Really appreciate your help.

Thanks,

Re: Set Analysis aggregate with date range

sunny_talwar — Tue, 12 Jun 2018 15:08:32 GMT

Can you let me know what was the exact expression you used?

Is it basically part of the set analysis in this case or still not?

Nope, this is not set analysis... this is an if statement now.