topic Simple dashboard in QlikView

Simple dashboard

AH — Sun, 14 Dec 2014 19:24:15 GMT

Hi,

Can anyone help me with this? In the Data file TotalLineAmount has negative values which indicates the credits. Gross amount is the sum of all positive and negative values.

I would really appreciate your help.

Thanks,

Shan

Re: Simple dashboard

Anonymous — Sun, 14 Dec 2014 19:31:18 GMT

But what problem are you facing? Can you please explain more.

Re: Simple dashboard

AH — Sun, 14 Dec 2014 19:34:37 GMT

I need to pull the credit amount in the text box and in the chart. I couldnt do that.

Would you help me with that?

Thanks.

Regards

Shan

Re: Simple dashboard

prieper — Sun, 14 Dec 2014 19:45:22 GMT

Not quite clear, what you need, but if you wish to sum credits and revenues separately, you may use a formula like

SUM(IF(Value < 0, Value)) -> credits

SUM(IF(Value >=0, Value)) -> revenues

SUM(Value) -> gross amount

HTH Peter

Re: Simple dashboard

AH — Sun, 14 Dec 2014 20:00:08 GMT

Thanks Peter fpr your helpful reply.

I want to categorize the amount of credit according to ItemID. SO, how do i want to combine these two expressions?

=SUM(IF(LineAmount < 0, LineAmount))

=num(Sum({<[ItemID] = {'A', 'B','C','L' }>} LineAmount),'##,##0')

Regards

Shan

Re: Simple dashboard

AH — Mon, 15 Dec 2014 07:24:57 GMT

What i actually want is to calculate the credit amount (negative LineAmount) for ItemID A,B,C and L. Gross & Net Quantity for ItemID A,B,C and L. Gross & Net Amount for ItemID A,B,C and L. Your reply contains for All ItemID not for specific items.

Thanks,

Shan

Re: Simple dashboard

ashishkalia — Mon, 15 Dec 2014 07:46:17 GMT

Hi,

Please check are u asking for something like this.
NOTE:- Check Dimension Tab , Expression Tab and Sorting Tab.

CHEERSSSSSSSSS..........

Re: Simple dashboard

AH — Mon, 15 Dec 2014 08:46:24 GMT

=num(Sum({<[ItemID] = {'A', 'B','C','L' }>} [Invoice Qty.]),'##,##0') This expression is giving me Gross Qty as 101

=num(Sum({<[ItemID] = {'A', 'B','C','L'}>}IF(LineAmount > 0,[Invoice Qty.])),'##,##0') this expression is giving me Net quantity as 115.

Seems like Gross Quantity is less than the Net quantity. That shouldnt be correct. Whats wrong with my expression?

Thanks,

Shan

Re: Simple dashboard

ashishkalia — Mon, 15 Dec 2014 09:40:53 GMT

ahmed Look into ur excel fields u are having -ve qty as well

EG. Qty= 10,11,12,-13,14,-4,21,-17

now Gross qty= 10+11+12+(-13)+14+(-4)+21+(-17)=34

and Net Qty = 10+11+12+14+21=68.

according to ur calculation formula u have implemented.

Re: Simple dashboard

AH — Mon, 15 Dec 2014 10:02:42 GMT

Yes that makes sense. Thanks for pinting that out Ashish.

Regards,

Shan