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: 
Anonymous
Not applicable

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
sunny_talwar

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)

View solution in original post

9 Replies
ecolomer
Master II
Master II

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

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

Anonymous
Not applicable
Author

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

sunny_talwar

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

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

Anonymous
Not applicable
Author

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

sunny_talwar

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?

sunny_talwar

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)

Anonymous
Not applicable
Author

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)



sunny_talwar

Try this expression:

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

Anonymous
Not applicable
Author

It working perfectly