topic Amount savings & aggr function in QlikView

Amount savings & aggr function

Wed, 30 Nov 2016 22:52:47 GMT

Hello everyone,

I have a basic need which is to create 2 graphs (bar charts) one that shows the amount of money saved per day and the other the amount of expenses per day.

The table I have is something like this:

Date	Label	Amount
30-06-2016	A	-73
30-06-2016	B	-24
30-06-2016	S	95
29-06-2016	C	38
29-06-2016	E	-5
29-06-2016	E	-42
29-06-2016	T	-53
29-06-2016	Y	-94
28-06-2016	A	-37
28-06-2016	F	38
27-06-2016	A	3476
27-06-2016	E	37

I tried to use aggr function but it does not give me the correct values.

The expression I used (dimension is the date of the day) is

=sum({$<Expense_or_Save={'Save'}>} Aggr(sum( Amount), Label))

And I created Expense_or_Save dimension in the script (expense if amount is negative otherwise save)

I think I am using the aggr function in a bad way but I don't know what is wrong....

Thank you for you help !

Re: Amount savings & aggr function

MarcoWedel — Thu, 01 Dec 2016 00:15:50 GMT

Hi,

maybe solutions could be:

hope this helps

regards

Marco

Re: Amount savings & aggr function

Thu, 01 Dec 2016 07:20:48 GMT

Hi Marco,

Thank you for your reply, actually you made me realize I do not necessarily need aggr function.

But it is still not showing the correct results.

But probably the problem lies elsewhere:

I noticed when I put my data in a straight table with Dimension 'Date' and 'Label' and have the expressions:

Amount and sum(Amount) that sometimes sum(Amount) equals 5 times the value of Amount, sometimes it is 4 times the value, sometimes 3 times the value, sometimes 2 times the value, sometimes equal.

I really don't understand...

Do you have an idea of the reason ?

Re: Amount savings & aggr function

MarcoWedel — Thu, 01 Dec 2016 22:25:06 GMT

Maybe you have multiple occurrences of the same records in your data.

Can you post a sample of your application?

thanks

regards

Marco

Re: Amount savings & aggr function

Fri, 02 Dec 2016 07:58:39 GMT

Hi Marco,

Yes it was one the question I was asking to myself, don't I need a column with unique IDs every time I work with a data set ? I am not sure of the answer but I would say yes, and for this I planned to concatenate the Label&Date&Amount to be sur it creates a unique ID (there might be other option like create 1, 2 3 ID with a for loop but I am not familiar with this).

But then I was stopped there because my problem is that I use a code that helps me consolidate the different excels that I have in one folder automatically all in one single table in Qlikview. So I tried my concatenate function but don't know where to place it. And plus I realized not all my excels are loaded every time... even though it was working not that long ago.

I attached the QV Code and the extract excels (dummy data created but same file structure). Sorry for this but the app contains some information I cannot share.

I am really motivated to make this work !