topic Re: sum two sum function in QlikView

sum two sum function

Anonymous — Fri, 20 Jan 2017 03:33:45 GMT

hey I am using this function in my table:

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)

and I cant get the result as expected, it only show the value of this expression (the last one): SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)

what am I doing wrong?

Re: sum two sum function

sushil353 — Fri, 20 Jan 2017 04:30:13 GMT

What are you trying to do with

"date(YearStart(MAX(DATEPERIOD))-31)" ?

Re: sum two sum function

Anonymous — Fri, 20 Jan 2017 04:46:06 GMT

I want to get the amount from december 2015.

the range of my date period is december 2015 - December 2016

what I am trying to do with the whole formula is to find the average of amount last year and amount date selected.

i.e. the selected month is July 2016 then the average is: (amount as per Dec 2015 + amount as per July 2016)/2

Re: sum two sum function

Anonymous — Fri, 20 Jan 2017 04:48:45 GMT

if i run the sum individually, it works.

but when i add it together it doesnt give me the value as I expected

Re: sum two sum function

sushil353 — Fri, 20 Jan 2017 05:01:48 GMT

Have you tried creating separate expression for two expressions.. ?

if possible attach a sample application

Re: sum two sum function

Anonymous — Fri, 20 Jan 2017 07:14:36 GMT

what do you mean by creating separate expression?

Re: sum two sum function

Anil_Babu_Samineni — Fri, 20 Jan 2017 07:18:17 GMT

Try with this expression in Text object and then see whether value is coming or not

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)

From Above, What if you select 02-01-2016(DD-MM-YYYY). Which value you want to get, I mean Arbitrary

Re: sum two sum function

stabben23 — Fri, 20 Jan 2017 07:29:22 GMT

Have you tried to separate them With parentheses?

(SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount))

(SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount))

Re: sum two sum function

Anonymous — Fri, 20 Jan 2017 07:40:03 GMT

yes I did. but still didnt work

Re: sum two sum function

Anonymous — Fri, 20 Jan 2017 07:44:34 GMT

when i run this in list box object:

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)

it works perfectly. but when I try in pivot table it doesnt

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)

my date period only contain the last day of every month e.g. 31-12-2015, 31-01-2016, 29-02-2016,....., 31-12-2016

if I select 31-01-2016 I hope to get amount of 31-12-2015

Re: sum two sum function

stabben23 — Fri, 20 Jan 2017 07:45:31 GMT

What if you create a straight table With the dimession(s) you want and makes two expression

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)

and then

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)

will it shows correct on row Level?

Re: sum two sum function

marcus_sommer — Fri, 20 Jan 2017 07:47:18 GMT

I suggest to check each part separately within a table-chart without an expression-label - then within the expression-label qlik will show you how the expression and especially the $-sign expansion will be interpreted - and quite probably you will see which part you need to adjust. Quite probably this will be the formatting-issue.

- Marcus

Re: sum two sum function

Anonymous — Fri, 20 Jan 2017 07:55:42 GMT

It works perfectly in straight table

Re: sum two sum function

stabben23 — Fri, 20 Jan 2017 08:00:43 GMT

Ok,

what if you add a 3:rd Expression column(1)+column(2)?

Re: sum two sum function

Anonymous — Fri, 20 Jan 2017 08:28:34 GMT

it works in straight table, but not in pivot table

I am using pivot table

Re: sum two sum function

Anil_Babu_Samineni — Fri, 20 Jan 2017 08:36:16 GMT

Ok, Can you send the application if possible

Re: sum two sum function

stabben23 — Fri, 20 Jan 2017 08:38:22 GMT

Then aggr() could help, can you show us what Dimension you have in Pivot.

Re: sum two sum function

Anonymous — Tue, 31 Jan 2017 08:56:44 GMT

sorry for the late reply.

it actually works fine if I dont select month as dimension. but if I select month as my dimension, it wont work

Re: sum two sum function

BrightFuture — Wed, 28 Nov 2018 08:48:31 GMT

I have the dame problem and I am aware where it comes from but could not find a solution. The problem comes from the fact that the "+" between the two expressions is not effective and only one of them will be calculated and shows as a result. So theoretically we need a "sum" function to sum the two expressions instead of the +, which I could not get right till now.

I will write you the answer when I find it 🙂

Re: sum two sum function

BrightFuture — Wed, 05 Dec 2018 14:31:17 GMT

So what I found is like using Total after the sum will get you some good results (only when you use one dimension), however when you use two dimensions for the X-axis you will get the same exact results every time (Let say you have Quaters and Years as your dimensions, by then you will get same values of the Quarters repeated over the years because Total ignores the dimension).

I am not sure how it will turn out in your expression but try this:

SUM(total{$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)