Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
bi_analysts_2
Creator
Creator

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

5 Replies
bi_analysts_2
Creator
Creator
Author

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

simon_minifie
Partner
Partner

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
Partner
Partner

Sorry, missed the date range part:

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

bi_analysts_2
Creator
Creator
Author

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

simon_minifie
Partner
Partner

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