topic Data is Summing in each record in QlikView

Data is Summing in each record

bkn — Thu, 02 Apr 2015 15:45:00 GMT

Hi All,

I used variable to calculate the next two columns, and when i used that variable to pull data, the data is summing in each records/Lines.

Please see the attachment.

Thanks in Advance.

Re: Data is Summing in each record

Anonymous — Thu, 02 Apr 2015 15:50:57 GMT

Could you share your variable and describe what you are hoping to achieve ?

Re: Data is Summing in each record

MK_QSL — Thu, 02 Apr 2015 15:51:31 GMT

Press

CTRL + ALT + V

Select Your variable

From Variable Definition

remove = from the front

i.e. If you define variable as

=SUM(Amount)

make it as

SUM(Amount)

Now use

$(variable) in your expression

Re: Data is Summing in each record

bkn — Thu, 02 Apr 2015 15:56:45 GMT

I am storing this expression in variables and then i call this variable in table.

=If(UnbilledMonthName='FEB', SUM({<UnbilledMonthName={'NOV'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='MAR', SUM({<UnbilledMonthName={'DEC'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='APR', SUM({<UnbilledMonthName={'JAN'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='MAY', SUM({<UnbilledMonthName={'FEB'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='JUN', SUM({<UnbilledMonthName={'MAR'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='JUL', SUM({<UnbilledMonthName={'APR'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='AUG', SUM({<UnbilledMonthName={'MAY'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='SEP', SUM({<UnbilledMonthName={'JUN'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='OCT', SUM({<UnbilledMonthName={'JUL'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='NOV', SUM({<UnbilledMonthName={'AUG'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='DEC', SUM({<UnbilledMonthName={'SEP'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter])

,If(UnbilledMonthName='JAN', SUM({<UnbilledMonthName={'OCT'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter])))))))))))))

Re: Data is Summing in each record

MK_QSL — Thu, 02 Apr 2015 15:58:23 GMT

Remove = from the start of variable declaration and try...

Re: Data is Summing in each record

Anonymous — Thu, 02 Apr 2015 16:01:17 GMT

... and you need the UnbilledMonthName as one of dimensions...

Re: Data is Summing in each record

bkn — Thu, 02 Apr 2015 16:05:55 GMT

Re: Data is Summing in each record

bkn — Thu, 02 Apr 2015 16:06:50 GMT

This is what i see now...When i insert expression in table instead of variables.

Thanks.

Re: Data is Summing in each record

bkn — Thu, 02 Apr 2015 16:07:25 GMT

I tried. Its not working.

Re: Data is Summing in each record

bkn — Thu, 02 Apr 2015 16:13:35 GMT

Hi Guys, it works now. The problem was "=" sign in variables. I removed that and got the exact result that desired. Thankyou for you time. Appreciated.

Re: Data is Summing in each record

bkn — Thu, 02 Apr 2015 16:20:55 GMT

How to use Variables in SUm Set analysis ??

=SUM({<Account={"Prime"}>}vPriorQuarter)

Re: Data is Summing in each record

MK_QSL — Thu, 02 Apr 2015 16:29:24 GMT

This is what I was saying to remove = from Variable definition since my 1st Reply...