18 Replies Latest reply: May 12, 2016 2:57 PM by subhasmita kar

# Volume as per current month and Revenue as per YTD

Hello All,

I need a help in finding out solution for my issue i.e. I have calculated my Volume as per current period(201603) selection and Revenue as per YTD(201603, 201602, 201601) logic. I am able to get the values for corresponding fields. Issue is that when I am selecting current period(201603), I should see only 2 rows(2nd table shown below), but I am getting all the rows because of my YTD logic for Revenue calculation. In conclusion I should see customers as per current period selection only not as per YTD. Please help on this.

 Input Data Customer Volume Revenue Period abc 90 67 201603 abc 89 34 201602 abc 59 81 201601 xyz 29 64 201603 xyz 45 92 201602

 Ouput Needed when selected period as 201603 Customer Volume Revenue Period abc 90 182 201603 xyz 29 156 201603
• ###### Re: Volume as per current month and Revenue as per YTD

Create a straight table with Customer and Period as dimensions

Expressions

1) =Sum(Volume)

2) =RangeSum(Above(Sum({<Period>} Revenue), 0, RowNo())) * Avg(1)

Output when nothing is selected

When 201603 is selected

• ###### Re: Volume as per current month and Revenue as per YTD

Thanks for your response guys. Sorry for late reply.

Hi Gysbert,

I have already done the YTD logic, but my issue is I want to see my data as per current selection only, as I am using YTD logic, it is pulling all the customer name and showing volume as 0 which does not fall under current selection for volume. Please find below example from one of your qvw. Here my requirement is to not show row for Pineapple as it does not have data for current month selection.

Hi Sunny,

Somehow the solution you provided is not working up for me. And I am not going to use period on my chart. Please suggest.

Thanks!

• ###### Re: Volume as per current month and Revenue as per YTD

Even without period as a dimension the solution seems to be working for me.

Would you be able to share a sample to look at?

Preparing examples for Upload - Reduction and Data Scrambling

QlikCommunity Tip: How to get answers to your post?

• ###### Re: Volume as per current month and Revenue as per YTD

I am not able to see anywhere to upload my sample file.

• ###### Re: Volume as per current month and Revenue as per YTD

Have you tried looking at this link: Uploading a Sample

• ###### Re: Volume as per current month and Revenue as per YTD

Sorry, I am unable to attach any sample file for security reason. Please find below screenshot, if it helps you.

• ###### Re: Volume as per current month and Revenue as per YTD

when not including period on your chart, Revenue is being calculated wrong, for xyz customer, revenue is wrong.

• ###### Re: Volume as per current month and Revenue as per YTD

I don't see any difference

• ###### Re: Volume as per current month and Revenue as per YTD

Hi Sunny,

For XYZ customer, revenue should come as 92+64= 156, as per my requirement. I guess, here in this case, it is summing up all the revenue despite of customers.

I would be using YTD condition on my expression for Revenue and want to see only those rows which falls under current period selection. It should not pull other rows because of YTD logic. Hope I am clear to you on my idea.

Thanks!

• ###### Re: Volume as per current month and Revenue as per YTD

How about the attached technique

• ###### Re: Volume as per current month and Revenue as per YTD

Hi Sunny,

Thanks for all your efforts.

I added one more customer ("rst") to the inline table. Now on my chart when I select period 201602, I am getting the row for rst, but I do not want to see as volume is not there for that customer for the period selected. Because of YTD calculation on revenue, the row is getting pulled. My revenue is dependent on volume, so if I don't have volume, I should not see revenue as well.

Inline script as:

Table:
LOAD * Inline [
Customer, Volume, Revenue, Period
abc, 90, 67, 201603
abc, 89, 34, 201602
abc, 59, 81, 201601
xyz, 29, 64, 201603
xyz, 45, 92, 201602
rst, 60, 85, 201603
rst, 20, 49, 201601

]
;

thanks!

• ###### Re: Volume as per current month and Revenue as per YTD

Try this expression:

=If(Sum(Volume) > 0, Sum({<Period = {"\$(='<=' & Max(Period))"}>}Revenue))

• ###### Re: Volume as per current month and Revenue as per YTD

This can resolve my issue. However, I have one question in my mind, are you sure, it would not yield some other issue. As I have other calculations which are based on these two calculations(volume,revenue).

Thanks for all your help!

• ###### Re: Volume as per current month and Revenue as per YTD

I cannot predict for 2 reasons

1) I don't know what those other calculations are

2) I don't know your database look like.

I don't see any issues for right now. Later on, if you find any issue, you can always come back here for help

• ###### Re: Volume as per current month and Revenue as per YTD

Thanks for all your effort and patience! Wish you a Great Day!