topic Re: conditional sum in QlikView

conditional sum

Tue, 19 Aug 2014 07:26:51 GMT

hello

i have a table like this:

id	month	d day	F Sr Net Sales Amount
1	1	25	5000
2	1	26	200
3	2	3	600
4	2	7	2500
5	2	9	2000

user asks to show him the last day sale in the month selected

i scripted like this< but it has some errors:

Sum ({< [d Day]= MaxString ([d Day]) >} [F Sr Net Sales Amount])

Re: conditional sum

tresB — Tue, 19 Aug 2014 07:29:07 GMT

Try:

Sum ({< [d Day]= {"$(=Max([d Day]))"} >} [F Sr Net Sales Amount])

Re: conditional sum

senpradip007 — Tue, 19 Aug 2014 07:30:32 GMT

Try this:

Sum ({< [d Day]= {'$(=Max([d Day]))'} >} [F Sr Net Sales Amount])

Re: conditional sum

ashfaq_haseeb — Tue, 19 Aug 2014 07:33:56 GMT

Try below

Sum ({< [d Day]= {'$(=Max([d Day]))'} >} [F Sr Net Sales Amount])

Regards

ASHFAQ

Re: conditional sum

MK_QSL — Tue, 19 Aug 2014 08:13:05 GMT

Create a straight table

Dimension

month

expression

=FirstSortedValue(DISTINCT Aggr(SUM([F Sr Net Sales Amount]),month,[d day]),-[d day])

You will have a syntex error for above expression, but just ignore it... You will have your answer...

Re: conditional sum

Tue, 19 Aug 2014 09:31:49 GMT

Hi Manish ,

Your Solution in working fine. I also tried solving it using this expression

=Sum({<[d day]={'=Aggr(Max([d day]),month)'}>}[F Sr Net Sales Amount])

But it is giving the incorrect Result

Answer is Like

Month , Sales

1 , 5000

2 , 600

I am not able find , why Result is coming like this

Thanks and Regards,

Vivek

Re: conditional sum

Tue, 19 Aug 2014 13:02:56 GMT

thank you,

well what would it be if i want this as the condition:

d Day <= A

"A" is something i get as the input

Re: conditional sum

ashfaq_haseeb — Tue, 19 Aug 2014 13:08:10 GMT

Try below

Sum ({< [d Day]= {'<=$(=Date(Max([d Day])))'} >} [F Sr Net Sales Amount])

Regards

ASHFAQ