how to select limited list into a chart qlik sense

[Anonymous]

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

[Anonymous]

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

[simon_minifie]

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

[simon_minifie]

Sorry, missed the date range part:

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

[Anonymous]

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

[simon_minifie]

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