Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

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
CustomerVolumeRevenuePeriod
abc9067201603
abc8934201602
abc5981201601
xyz2964201603
xyz4592201602

   

Ouput Needed when selected period as 201603
CustomerVolumeRevenuePeriod
abc90182201603
xyz29156201603
Tags (1)
1 Solution

Accepted Solutions

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

Try this expression:

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


Capture.PNG

18 Replies
Highlighted

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

See this document: Calculating rolling n-period totals, averages or other aggregations

Or this blog post: Accumulations


talk is cheap, supply exceeds demand

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

Capture.PNG

When 201603 is selected

Capture.PNG

Not applicable

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.

Capture.PNG

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?

Uploading a Sample

Not applicable

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

Not applicable

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

If security is concerned, consider scrambling the data

Preparing examples for Upload - Reduction and Data Scrambling

Not applicable

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

I am not allowed to upload any document. Its showing warning to me.

Community Browser