- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
*in the last session in the proportion section: instead of sales it should be costs
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, missed the date range part:
If(Sum(Sales)<100 and Date<=Today() and Date>=Today()-8, Sum(Sales) / Sum(Costs))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
"try Date as the dimension" - but i want to present the top publishers, how does can it work?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, my mistake... It'll be the publishers as the dimension, or add as well as date.