5 Replies Latest reply: Oct 31, 2017 8:27 AM by Simon Minifie

# how to select limited list into a chart qlik sense

Hi,

I've loaded a data set that contains 4 columns:

• Date (since the beginning of the month)
• Account
• sales
• costs

Then, I would like to create a bar chart with the top 10 accounts by the proportion: sales/costs (total sum of all history up to week ago from now)

But, I want to consider only accounts with over then \$100 sales in total on the exact date of week ago

When I try to apply this in the dimension settings:

if(Date=today()-8 and sum(sales)>100,account)

I’m getting an error (‘invalid dimension’)

When I try to apply this in the measure settings:

if(

sum(

if(

Date=Today()-8,Sales

)

)>100, sum(

if(

Date<Today()-7,sales

)

)

/

sum(

if(

Date<Today()-7,sales

)

)

)

I get weird account list with zeros values for the proportion (seems like for part of the account list the measure is not calculated due to the if condition)

A little bit long example but hope you will find a place in your heart to help

Tnx!

Tal

• ###### Re: how to select limited list into a chart qlik sense

*in the last session in the proportion section: instead of sales it should be costs

• ###### Re: how to select limited list into a chart qlik sense

Hi Tal,

Try Date as the Dimension, and this as the Measure:

If(Sum(Sales) >100 and Date=Today()-8, Sum(Sales) / Sum(Costs))

Thanks,

Simon

• ###### Re: how to select limited list into a chart qlik sense

Sorry, missed the date range part:

If(Sum(Sales)<100 and Date<=Today() and Date>=Today()-8, Sum(Sales) / Sum(Costs))

• ###### Re: how to select limited list into a chart qlik sense

"try Date as the dimension" - but i want to present the top publishers, how does can it work?

• ###### Re: how to select limited list into a chart qlik sense

Sorry, my mistake... It'll be the publishers as the dimension, or add as well as date.