topic Re: Aggregation and set analysis. in QlikView

Aggregation and set analysis.

tom2qlik — Thu, 10 Aug 2017 09:53:14 GMT

Hi all,

I was trying to replicate a chart total in another chart as an expression. The value I was trying to create was the TOTAL of my cost expression as shown below.

To do this I simply aggregated the expression by Date, L and ID which provided the correct value of £5697.85.

=MONEY(SUM(aggr(SUM(distinct {<DataSource={'Waste'}, Last7Days={1}, WeekDay={'SUN'}>}Cost)*
SUM({<DataSource={'Waste'}, Last7Days={1}, WeekDay={'SUN'}>}ActualWeight), Date, L, ID )), '£#,##0')

I completed this expression in a textbox which worked however when I put it into the new chart which was using a value list the expression no longer worked and return a value of £0

After a bit of research I found that aggregation within a value list doesn't seem to work as one would expect therefore I devised a new solution.

=MONEY(SUM({<DataSource={'Waste'}, Last7Days={1}, WeekDay={'SUN'}>}Cost*ActualWeight), '£#,##0')

New solution worked within the value list chart and I believed the outcome had be achieved and moved on. Next day I rebuild my app and low and behold the working value within the chart has changed from the correct value of £5697.85 to £11396

I have checked the data and done the calculation manually and it should still equal £5697.85. No data for that day has changed.

Can anyone explain what I've missed?

Appreciate any advice.

Tom

Re: Aggregation and set analysis.

tresB — Thu, 10 Aug 2017 10:12:57 GMT

I guess, it would be something related to date comparison. Your date flags like Last7Days changes on every different day reload, if you reload today, Last7Days flag may get 10,9, 8,7,6,5,4 days of Aug, while if you reload it tomorrow, the flag would mark 11,10..6,5 days of Aug and eventually your expression gives different result.

Hope this helps.

Re: Aggregation and set analysis.

tom2qlik — Thu, 10 Aug 2017 10:35:21 GMT

Thanks for your input.

And you're right, each day the it rebuilds the days with the flag change. So Weekday 'SUN' is the 6th of Aug and will have a value of 1 for Last7Days. Eventually yes the value will change to the most recent weekday but for this it in this case it has not changed yet. Therefore I can't see why the value would change when the data for that set analysis stays the same.

Re: Aggregation and set analysis.

tresB — Thu, 10 Aug 2017 10:42:01 GMT

That would depend on how (the statement you used) you created the flag in the script. Could you post your app sample or the script snippet at least?

Re: Aggregation and set analysis.

tom2qlik — Thu, 10 Aug 2017 11:03:59 GMT

The Last7Days flag is created with the following code:

If(Today()-DateLink>=0 and Today()-DateLink<7,1,0) AS Last7Days

It might be difficult to get a sample app.

Re: Aggregation and set analysis.

sunny_talwar — Thu, 10 Aug 2017 11:11:15 GMT

It appears that your data seems to be duplicating somewhere... because 5,697.85*2 = 11,395.7.... May be there you need to change your data model a little bit to avoid this duplication?

Re: Aggregation and set analysis.

tom2qlik — Thu, 10 Aug 2017 14:10:30 GMT

Thanks Sunny, the qvd had started creating duplicates and this is what was causing the issue. Above formula still works.