Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bijoylalu
Contributor

Dynamic values for Dimension in Visualization

Hi,

          I have a month field in data-source (having values from 01 to 12 representing each month), which i want to use as dimension in my Combo Chart. But i want to apply a dynamic case for the dimension.

          I have a month selection in my application.If i am selecting 06, then my dimension in the chart should show values from 01 to 06, if i am selecting 07, then dimension should show value from 01 to 07. If i am not selecting any month, then it should should all values(01 to 12).

        How can i create an expression for the same ??

1 Solution

Accepted Solutions

Re: Dynamic values for Dimension in Visualization

I am attaching a sample for you

Capture.PNG

Script:

Table:

LOAD *,

  Month(Date) as Month,

  Num(Month(Date)) as MonthNum;

LOAD * INLINE [

Date, Sales

1/1/2016, 30

2/1/2016, 32

3/1/2016, 42

4/1/2016, 26

5/1/2016, 52

6/1/2016, 15

7/1/2016, 59

8/1/2016, 45

9/1/2016, 36

10/1/2016, 62

11/1/2016, 12

12/1/2016, 52

];

Dimension

Date

Expression:

Sum({<MonthNum = {"$(='>=' & '01' & '<=' & MaxString(MonthNum))"}>} Sales)

9 Replies
ecolomer
Honored Contributor II

Re: Dynamic values for Dimension in Visualization

You can Alternate states for have Month in a list box.

In the combo chart, you put condition Month <= Month selected in Alternate State

bijoylalu
Contributor

Re: Dynamic values for Dimension in Visualization

Hi Enrique,

                  Where can i download a working version of list box for QlikSense??

                  The one I downloaded from branch is not working properly.

                  Can you suggest me a link for the same??

Re: Dynamic values for Dimension in Visualization

May be just use set analysis. Assuming your expression is Sum(Sales), change it to this:

Sum({<Month = {"$(='>=' & '01' & '<=' & MaxString(Month))"}>} Sales)

bijoylalu
Contributor

Re: Dynamic values for Dimension in Visualization

But it will show Sum(Sales) from Jan to Apr when i selected 'Apr' and in dimension it will show only Apr as show below

      Capture.JPG

    I want to show, Jan, Feb, Mar and Apr in the X-Axis with its sum(Sales) respectively

Re: Dynamic values for Dimension in Visualization

You dimension is showing Apr? Is that the Month field? I thought you said Month is 01, 02, 03? or is it Jan, Feb, Mar?

Re: Dynamic values for Dimension in Visualization

I am attaching a sample for you

Capture.PNG

Script:

Table:

LOAD *,

  Month(Date) as Month,

  Num(Month(Date)) as MonthNum;

LOAD * INLINE [

Date, Sales

1/1/2016, 30

2/1/2016, 32

3/1/2016, 42

4/1/2016, 26

5/1/2016, 52

6/1/2016, 15

7/1/2016, 59

8/1/2016, 45

9/1/2016, 36

10/1/2016, 62

11/1/2016, 12

12/1/2016, 52

];

Dimension

Date

Expression:

Sum({<MonthNum = {"$(='>=' & '01' & '<=' & MaxString(MonthNum))"}>} Sales)

bijoylalu
Contributor

Re: Dynamic values for Dimension in Visualization

Hi Sunny,

                This logic is working for me. This is working perfect with YTD data for me(Sum of Amount is taking directly for each month).

              What if i want the chart to display MTD data(Actual data is stored as YTD, MTD is determined by subtracting previous month amount from current month).

For instance, the below script stores my YTD data,

LOAD * INLINE [

Date, Sales

Jan, 30

Feb, 35

Mar, 42

Apr, 48

May, 56

Jun, 63

Jul, 68

Aug, 73

Sep, 79

Oct, 85

Nov, 92

Dec, 99

];

MTD for Sep = YTD for Sep - YTD for AUG. (79 - 73 = 6)

MTD for Jun = YTD for Jun - YTD for May. (63 - 56 = 7)


The logic for graph is the same as above mentioned.(Selection of JUN should show months from JAN to JUN in Graph)



Re: Dynamic values for Dimension in Visualization

Try this expression:

Sum({<MonthNum = {"$(='>=' & '01' & '<=' & MaxString(MonthNum))"}>} Sales) - Alt(Above(Sum({<MonthNum = {"$(='>=' & '01' & '<=' & MaxString(MonthNum))"}>} Sales)), 0)

bijoylalu
Contributor

Re: Dynamic values for Dimension in Visualization

It working perfectly