topic Re: Formula Help in QlikView

Formula Help

Wed, 24 Sep 2014 18:40:32 GMT

I am going to try my best to articulate what it is I am struggling with. I have a straight table that shows credits and non credits what I am trying to do is create a formula that would add up the credits for a total $ as well as the same for non credits and then present a third dimension as a percentage of the two.

For example: % of total credits compared to total sales $ "Basically credits as a percentage of total sales." The problem is that the both the credits and sales are in the same table field. I have been racking my brain on this one and I am sure it is something simple.

Any help would be greatly appreciated.

Re: Formula Help

vgutkovsky — Wed, 24 Sep 2014 18:45:18 GMT

It's a little hard without example data to work with, but guessing you need something like this: sum({<Amount={">0"}>} Sales)/sum(Sales)

Or if you have an indicator that something is a "credit" then sum({<Credit={'Y'}>} Sales)/sum(Sales)

Vlad

Re: Formula Help

Wed, 24 Sep 2014 18:57:03 GMT

Thanks I will give it a shot and let you know how it works out.

Re: Formula Help

Thu, 25 Sep 2014 18:15:32 GMT

No luck but thanks anyways Vlad.

Re: Formula Help

jyothish8807 — Thu, 25 Sep 2014 18:25:03 GMT

Hi Robert,

Can you share a sample data or screenshot, it will make easy for us to understand.

Regards

Re: Formula Help

Thu, 25 Sep 2014 18:46:44 GMT

What I need to do is create three additional expressions, one that shows the total sales excluding the credits, another showing the credits only, and a third that calculates the percentage between the two.

Essentially what I need to know is the % of credits compared to total sales across multiple dimensions.,

Re: Formula Help

Thu, 25 Sep 2014 21:08:16 GMT

Re: Formula Help

Thu, 25 Sep 2014 21:09:46 GMT

Sorry I tried attaching the picture in the first reply.

Re: Formula Help

Mon, 29 Sep 2014 13:40:54 GMT

What I have is a single dataset showing both positive and negative numbers, what I am trying to accomplish is the creation of two expressions. One showing all of the positive numbers and the second showing the negative numbers. I have been playing with the syntax but can’t seem to get it right.

Re: Formula Help

vgutkovsky — Mon, 29 Sep 2014 15:15:11 GMT

If you have the 2 "credit reason" columns in your chart, then you'd need to aggregate away from these columns in order to be able to present a single view of credits and non-credits per customer/item. You would accomplish that by using the "total" keyword in your sum. Not exactly sure which expression you had in mind, but try something like this:

Positives: sum({<Sales={">0"}>} total <[Customer Name],[Item Number]> Sales)

Negatives: sum({<Sales={"<0"}>} total <[Customer Name],[Item Number]> Sales)

Replace "Sales" with the number field you used to create "Product_Only $"

Alternatively, if you don't have the "credit reason" columns, you can get rid of "total <[Customer Name],[Item Number]"

Regards,

Vlad

Re: Formula Help

Wed, 22 Oct 2014 11:29:40 GMT

Thank you, this solved my issue.