Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

New to Qlik, typical Excel like question

Hi, I'm fairly new to Qlik and I consider myself to be a power Excel User along with Data pull capabilities within SQL. But that's probably as far as my skill set is currently. So my organization is in the process of adopting Qlik as being their new main reporting tool and I've fortunately been tasked with converting all of these wonderful Excel Reports into something more manageable with drill down capabilities once connected to the data source.

So my question is a typical Excel like question, where I was having difficulties understanding how to utilize a Dynamic Value within an Expression but to specify what single Value within said expression. So what I mean specifically is that I have something you'd be accustomed to seeing such as Sales within Various Regions. However, not all of my sales have been captured or tagged appropriately to a given region.

So to be even/fair, I want to redistribute that value back to the other regions based on the percentage distribution of the regions. Confused yet? It's really not that bad.

Here is what I mean:

QlikQuestion.PNG

So as you can see above I have my Product Sum Distribution in the 6th column and essentially I just want to multiply that value to the Region 'No State Attached' (So for this given period, it is 687) and basically I want a 7th column that lists 687 multiplied to column 6. Obviously I need this to be dynamic so that when I change my Period (Column 1) everything adjusts to the new value of RGN='No State Attached' I tried to google search this and I didn't come up with anything.

Any help would be greatly appreciated, and again I'm pretty new but I feel like this is something I might have to do quite bit given the reports that I am working with.

Best, Bryan.

1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor

Re: New to Qlik, typical Excel like question

Hi Bryan,

Create a straight table with Period, PGMJ and RGN as dimension. Then add following three expressions:

1.  sum(Quantity)

2.  sum(Quantity)/sum(Total Quantity)

3.  sum({<RGN={'No State Attached'}>}Total Quantity) * sum(Quantity)/sum(Total Quantity)

Unbenannt.png

hope this helps

4 Replies
Highlighted
Partner
Partner

Re: New to Qlik, typical Excel like question

Bryan,

Can you post a simplified version of the QVW so we can work on this without recreating the data.

Thanks,

Craig

Frank_Hartmann
Honored Contributor

Re: New to Qlik, typical Excel like question

Hi Bryan,

Create a straight table with Period, PGMJ and RGN as dimension. Then add following three expressions:

1.  sum(Quantity)

2.  sum(Quantity)/sum(Total Quantity)

3.  sum({<RGN={'No State Attached'}>}Total Quantity) * sum(Quantity)/sum(Total Quantity)

Unbenannt.png

hope this helps

Not applicable

Re: New to Qlik, typical Excel like question

Actually I tried what you use and even reviewed in your screenshot however, there is still an issue here. Why would the value within 'No State Attached' list something like 1.2 or something around a 1? Shouldn't this be 0?

Even your screenshot shows a value there. We want to re-distribute the value to the 'Other' regions.

qlick2.PNG

Circled in Red (Shouldn't this be zero? I'd like it to be zero even if I were to round down it's still going to be one and not zero)

qlick3.PNG

Not applicable

Re: New to Qlik, typical Excel like question

Nevermind I got it, had to play with rounding. Rounding in this system is very complex when transitioning from Excel as it's very scientific it seems compared to format which is what I'm accustomed to seeing in excel. Maybe I'm a noob here but I feel like there could be a lesson in itself on how rounding works here at least for users who do not have a strong programming background.

Thanks for your help.

Best, Bryan.