Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

mhmhmh91
New Contributor

Display decreasing trend of data in bar chart

Hi,

I have a set of data which has below columns

ItemInventoryDaily ConsumptionDays of Inventory = Inventory / Daily Consumption
11000040025
22000080025
31500050030

Can I display a bar chart which shows the decreasing trend of items? Take item 1 for example.

DayInventory
19600 (10000-400)
29200
38800
...
250

If one item is selected, the bar chart shows the decreasing trend of this item. If no item is selected, the bar charts shows the decreasing trend of total inventory.

Thank you in advance!

Mandi

1 Solution

Accepted Solutions

Re: Display decreasing trend of data in bar chart

So I didn't do it exactly the way you asked, but I think the functionality is the same...

I took [Day Number] out of the load script to implement it dynamically, so the load script piece that I added previously now looks like this:

SET vStartDate = MakeDate(2017, 1, 1);

SET vNumDays = 100;

Dates:

LOAD date($(vStartDate) + IterNo() - 1) AS [Date]

AutoGenerate 1

WHILE IterNo() <= $(vNumDays);

You need to set the vStartDate and vNumDays to cover the full range of days that you want available to the user to select.  You can make this range as large or small as you'd like.

Then, on the dashboard itself, I added a new variable vChartStartDate, which I added a calendar object that the user can select dates from.  I set the Min Value to =min(Date) and the Max Value to =max(Date).

Dimension:

=if(Date >= vChartStartDate, Date)

Expression:

if(

  sum(Inventory)-sum([Daily Consumption])*RowNo() >= 0,

  sum(Inventory)-sum([Daily Consumption])*RowNo()

)

I've attached the updated example file.  You can see that as you select new dates in the vChartStartDate calendar object, it changes the first date on the charts.

8 Replies

Re: Display decreasing trend of data in bar chart

Something like this should work:

Dimension (you may need to adjust the second argument depending on the amount of data you have--I just set it to 100 for this example):

=ValueLoop(1, 100, 1)

Expression (you'll need to adjust the second argument to be the same as in the dimension--just like I have 100 here):

if(

  sum(Inventory)-sum([Daily Consumption])*ValueLoop(1, 100, 1) >= 0,

  sum(Inventory)-sum([Daily Consumption])*ValueLoop(1, 100, 1)

)

I've also attached an example file.

mhmhmh91
New Contributor

Re: Display decreasing trend of data in bar chart

Hi Nicole,

Thanks for your help. Is it possible to start the valueloop from a date? I mean for each bar, the label is date instead of 1,2,3,...100.

Thanks,

Mandi

Re: Display decreasing trend of data in bar chart

Okay, so for using dates instead of integers...

I added this to the load script to create a date/calendar table:

SET vStartDate = MakeDate(2017, 1, 1);

SET vNumDays = 100;

Dates:

LOAD date($(vStartDate) + IterNo() - 1) AS [Date],

  IterNo() AS [Day Number]

AutoGenerate 1

WHILE IterNo() <= $(vNumDays);

You can adjust the start date and the number of days to whatever you need them to be (I used 100 since that is what I had used previously in the value loop).

Then I updated the table to have:

Dimension:

Date

Expression:

if(

  sum(Inventory)-sum([Daily Consumption])*[Day Number] >= 0,

  sum(Inventory)-sum([Daily Consumption])*[Day Number]

)

I've attached an updated example file as well.

mhmhmh91
New Contributor

Re: Display decreasing trend of data in bar chart

Hi Nicole,

Thank for the reply. In the solution you provided, is it possible to set vStartDate varied as the largest date among date selections users select in Dashboard?

Thank you very much,

Mandi

Re: Display decreasing trend of data in bar chart

So I didn't do it exactly the way you asked, but I think the functionality is the same...

I took [Day Number] out of the load script to implement it dynamically, so the load script piece that I added previously now looks like this:

SET vStartDate = MakeDate(2017, 1, 1);

SET vNumDays = 100;

Dates:

LOAD date($(vStartDate) + IterNo() - 1) AS [Date]

AutoGenerate 1

WHILE IterNo() <= $(vNumDays);

You need to set the vStartDate and vNumDays to cover the full range of days that you want available to the user to select.  You can make this range as large or small as you'd like.

Then, on the dashboard itself, I added a new variable vChartStartDate, which I added a calendar object that the user can select dates from.  I set the Min Value to =min(Date) and the Max Value to =max(Date).

Dimension:

=if(Date >= vChartStartDate, Date)

Expression:

if(

  sum(Inventory)-sum([Daily Consumption])*RowNo() >= 0,

  sum(Inventory)-sum([Daily Consumption])*RowNo()

)

I've attached the updated example file.  You can see that as you select new dates in the vChartStartDate calendar object, it changes the first date on the charts.

mhmhmh91
New Contributor

Re: Display decreasing trend of data in bar chart

Can you provide more details about creating vChartStartDate? I am not sure whether I understand correctly. After I create the vChartStartDate variable, what its expression should be?

PS: I did not find the example file...

Thank you very much,

Mandi

Re: Display decreasing trend of data in bar chart

Mandi Hu wrote:

Can you provide more details about creating vChartStartDate? I am not sure whether I understand correctly. After I create the vChartStartDate variable, what its expression should be?

You do not need to put an expression for it because the calendar object will set the value in it.

Mandi Hu wrote:

PS: I did not find the example file...

You have to open the thread (you will not be able to see the file if you're just looking at my reply in your inbox).

mhmhmh91
New Contributor

Re: Display decreasing trend of data in bar chart

Thank you so much!

Community Browser