Dimension switcher and limiter

[Anonymous]

Hi all,

I have created a master fiscal calendar with the following fields:

• CalendarYear
• CalendarQuarter
• CalendarMonth
• CalendarWeek
• CalendarDate

I have been working for a while on a way to show the last 10 items of a series. I took the CalendarWeek dimension as an example: i wanted to show on a chart the last 10 CalendarWeeks based on the most recent CalendarDate uploaded as part of my data. I therefore included the below set analysis in my formula

{$<CalendarDate={">$(=Date(Max(CalendarDate)-70))<=$(=Max(CalendarDate))"}>}

As you can see, it tells the chart to only list the last 10 weeks by telling it to take the range of dates between the most recent date and the previous 70 dates to get the 10 weeks range desired.

Works beautifully!

However, I now would like to be able, at the push of a button, to switch through the dimensions and that it continues to show the last 10 entries of any dimension. I searched for extensions that would allow me to do that but I am at a dead-end.

Let me clarify what i would like to achieve:

- Have a series of buttons or other controls that would change the dimension of all charts so if you press the "Yearly" button, it switches all charts dimensions to CalendarYear; press the "Quarterly" button, it switches all charts dimensions to CalendarQuarter; etc. etc.

- Have a Variable listen to the chosen dimension and changes the number of days if should use to get the range of data. It would choose 70 days if you choose the "weekly" dimension, 300 if you choose the "Monthly" dimension. I can then change my set analysis above to include the newly created variable. This part should be simple enough... i think.

Does anybody know how these 3 can be achieved? Having the controls, the change in the charts and the listening variable?

[Anonymous]

Hi,

There is "add alternative" option in Sense. Did you try that? Would that do the trick?

Best Regards

Serhan

[Anonymous]

You could use an 'large' formula with 'if' selected = Year, then... etc. But it that works (as you have months and years with different amount of days).

The alternative state is perhaps usefull (Qlik Sense Alternate States - The Full Set - YouTube).

Or take a look a Qlik Branch (Qlik Branch), such as: http://branch.qlik.com/#!/project/56728f52d1e497241ae698a0

[Oleg_Troyansky]

In order to achieve what you described, you need to have a table in your dataset, with all possible Date dimensions in one field, and the corresponding Set Analysis filters in the other field. Let's call these fields DateDim and DateDimFilter

Then, you should have two variables that store the current values of the two fields:

vDateDim = '=only(DateDim)'

vDateDimFilter = '=only(DateDimFilter)'

In your chart, the Chart dimension should be:

$(vDateDim)

and the chart Measure should read:

sum({<$(vDateDimFilter)>} Value)

that should do the trick.

cheers,

Oleg Troyansky

Learn advanced QlikView and Qlik Sense techniques in my book QlikView Your Business

[ogster1974 · Accepted solution]

Here is a working example of a solution you could possibly use.

It uses the QSVariable extension to create the button to populate the variable and you can see in the chat the logic to change, titles, dimensions and measures.

Makes Sense to go round in circles

Hope this helps.

Regards

Andy

[qlikviewajeet]

Button is only showing Month bcz you passed 3 into variable while creating and no selection is working on Button.

Fast change Dimension or measure on button selection

any solution, If button click can work..

Regards

Ajeet