Dynamic Month Column base on Filter Selection

[VL126]

Hi,

Would like to see how i could display diff columns depends on the selection from the filter pane.

Say my table got a month column (Jan to Dec), a name column and an amount column. Month is on filter pane.  Want to display the data with Names on row, Month  in COlumn and amount as measure.  When say Month is selected eg Apr,  then column should shows,  Jan to Apr individually.  When Jun is selected, show Jan to Jun individually in the column. Any quick way of doing that?

Thanks,

Victor

[brunobertels]

try 

This 

Create a variable called vMonth

Then Add variable Object 

create 12 values ( Value = 1 , Name = Jan  and so on 

Then in your dimension add this : 

If(num([DATE.autoCalendar.Mois]) >= '1' and num([DATE.autoCalendar.Mois]) <= $(vMois),[DATE.autoCalendar.Mois])

Depending of the selected month in your variable drop down button it will restrict month value.

Hi 

Note tested but i would first for the filterpane use a valuelist function with month ( valuelist(Jan, feb, march, April ... ) 

Then in a pivot table create a calculated dimension with a if statement to restrain the dimension to the desired month 

[VL126]

Thanks. I am still new to Qlik Sense so not quite sure how to implement what you suggest.

I was thinking, the Month from Filer ie Mois, i can Num(Mois) is >= Num(Month in my data table), then Month in my data table should display. That said, how to display the Month in my data table is something that i am struggling. 

Appreciate if you could guide me through.

[brunobertels]

Hello, 

I assume that you have a date field in your database. 

Depending of your data model you may have created a calendar using your date field ( E.g : week(date) as week, Month(date) as Month ... )  or you have let qliksense create you an automatic calendar derived from your date field. Then you may have in your script an autocalendar and dimension named like date.autocalendar.date, date.autocalendar.month etc .. 

So you can use the num function to get the integer of relevant month : num(Month) will give 1 for jan, 2 for feb, 3 for march etc.

So in your cross table use your month dimension like this : 

If(num(YourMonthDimension) >= '1' and num(YourMonthDimension) <= $(vMois),[YourMonthDimension)

it will display month strictly > to 1 ( Jan) and equal to the number of month of the result of your variable vMois.

hope it helps 

Bruno

[VL126]

apologies, tried something like this but not successful.

1. My data has  a "Month" and "Month _ID" columns

2. In my output table, under column field, I have " =If(num(MONTH_ID) >= '1' and num(MONTH_ID) <= '$(vMonth)',Month)".

3. Also create a variable called vMonth which equals Num(Month).

4. Month is also set in the filter Pane.

However when selecting one Month, only that month shows up in the output table.

[brunobertels · Accepted solution]

Hi 

I check your excel file and you will find attach a sample app with desired need. 

I used the ORDERDATE field to make the App rather than your MONTH or MONTH_ID field because : 

Your Month_ID Field seems not to be corresponding with your dateorder field or month field Eg : 

MONTH  = Jan but MONTH ID = 7 ( I dont know if it is a normal behaviour or an issue with your sample data) 

that say , 

create a variable in the variable panel called vMonth and give it the value 1 ( so that by default the variable button will be selected on January ) change the default value to get another selected month number by default. 

using Variable Object , create 12 values as explain before 

then add a pivot table object 

add dimension : PRODUCTLINE inline and in column add this calculated dimension : 

=If(num(month(ORDERDATE)) >= '1' and num(month(ORDERDATE)) <= '$(vMonth)',Month(ORDERDATE))

then add your mesure (here Sum(Sales) ) 

You may have this : 

Regards 

Bruno 

PS : i saw that your DATEORDER field is in french format , if ever french is your native language , it's mine too 

[VL126]

Thanks so much. I was struggling a bit in setting up the 12 values, managed to do that at the end.