Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Trouble indexing in Qlik Sense

Hey guys,

I have a line graph with 2 dimenions and a measure.

MonthYear, (x-axis)

Commodity,

Price (measure)

And unfortunately I have a couple problems which I'll break down

1. Set analysis

Thanks to this blog I've sort of got an indexing formula sorted which is

sum(Price)/(sum( total <Commodity> if(month(Date(MonthYear))=month(min(total Date(MonthYear))),Price))/100)


However I am trying to use set analysis of it so only Category 1 commodities are included. I know how to include it in the first Sum() just fine (ie. {$<Category = {1}>}), but I'm unsure about the second Sum().


2. MonthYear

I am unsure if I have included MonthYear correctly into the formula. MonthYear is loaded as:

Date(monthstart(TempDate), 'MMM-YY') AS MonthYear

I ask if this is correct because I am experiencing issues with the MonthYear dimension in my line chart.

My table has 4 fields. Date, Commodity, Price, Volume.

Volume has dates stemming back to Sep 12 while Price only has values starting Jan 14. However when I have the formula in, my indexation starts from Sep 12, while I want it to start at Jan 14 because that's when I first have data. The rest is blank.

I've tried using Limitation to > 0, but that's not working either. Instead it starts from Sep 12 and ends up looking like this.

Capture3.PNG

When I select the months which have Price data, which is Jan-14 to Jan-15, it still doesn't work.

Capture2.PNG

But when select the 2014 year, it works perfectly.

Capture1.PNG

Does anyone know how to fix these issues?

Thanks in advance for your help!

5 Replies
Not applicable
Author

Hey guys,

Apologies for bumping this question back up, but would anyone know how to do this?

jonathandienst
Partner - Champion III
Partner - Champion III

Your first question - to add the Category set filter:

sum({<Category = {1}>} Price) / (sum({<Category = {1}>} total <Commodity> if(MonthYear = min(total MonthYear)), Price) / 100)

But you could also remove the If() clause:

sum({<Category = {1}>} Price) / (sum({<Category = {1}, MonthYear = {"$(=min(MonthYear))"}>} total <Commodity> Price) / 100)

If that does not work, you may need this version:

sum({<Category = {1}>} Price) / (sum({<Category = {1}, MonthYear = {"$(=Date(min(MonthYear))"}>} total <Commodity> Price) / 100)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

For your second question - is Price null or zero before Jan 2014? If its null, then

     min({<Price>} MonthYear)

will return the first month with a non-null Price. If is is zero, then

     min({<Price -= {0}>} MonthYear)

will return the first month with a non-zero price (actually the first non-zero AND non-null value). The -= may show as a syntax error (it does in Qlikview) but that is a bug in the QV expression editor.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hey Jonathan, thanks for your reply.

In your first post, the second equation worked for me perfectly in regards to the set analysis so thank you!

The price before Jan 2014 is null so I inputted the part in your second post to formulate this:

sum({<Category = {1}>} Price) / (sum({<Category = {1}, MonthYear = {"$(=min({<Price>} MonthYear))"}>} total <Commodity> Price) / 100)

But as a precaution to any zero's in my data (there is no reason for the price to be zero) I changed the equation to:

sum({<Category = {1}>} Price) / (sum({<Category = {1}, MonthYear = {"$(=min({<Price -= {0}>} MonthYear))"}>} total <Commodity> Price) / 100)

And it has no syntax error.

This is nearly working, however I'm experiencing a couple problems. By default instead of the Min MonthYear being Jan 14 it is Sep 12, I'm not able to select the MonthYears on the line chart by clicking on it or drag selecting, and no data is actually shown on the chart. But I can select the years 2014 and 2015 in a filter pane and the chart works perfectly with the data showing from Jan 14 to Jan 15, and it then allows me to select the MonthYear on the chart.

Hopefully there's a way to fix these problems. Your help is very much greatly appreciated!

Edit:

I just realised that I'm experiencing another issue.

I'm not able to click on the commodity in the legend, and more than the specified number appears. Category 1 only contains 4 different commodities, and only 4 show on the chart, but all commodities show up in the legend (about 12). I'm also not able to click on the commodities, just the same as the MonthYear. However when I click on 1 in the Category filter pane, it seems to work fine.

Not applicable
Author

Hey guys, just an update to this thread. I've decided to just chuck in the figures dating back to Sept 12 so now it seems to be working alright. However I'm still experiencing a couple issues.

I'm not able to click on a single MonthYear on the x-axis. It just doesn't seem to work. And Even though I've specified to only shopw 4 commodities, on the legend every commodity is shwon (as you can see by the different colours in the legend in the below pic).

qlik3.PNG

Does anyone know how to fix those 2 issues? If the commodity issue can't be fixed I'll just chuck the ones I need in a new field as my second dimension and remove the set analysis.