Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
pirtekmismanage
Contributor
Contributor

Set Analysis aggregate with date range

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.

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

10 Replies
sunny_talwar

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

ogautier62
Specialist II
Specialist II

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

vikramv
Creator III
Creator III

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

====

pirtekmismanage
Contributor
Contributor
Author

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.

Capture No selection.PNG

Capture With Selection.PNG

pirtekmismanage
Contributor
Contributor
Author

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.

pirtekmismanage
Contributor
Contributor
Author

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.

pirtekmismanage
Contributor
Contributor
Author

Sunny,

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

Please see the sample file attached.

sunny_talwar

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

Capture.PNG

pirtekmismanage
Contributor
Contributor
Author

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,