topic Re: Show all even when date is selected in QlikView

Show all even when date is selected

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

This is a strange question ... I know before I even start but ....

I have a page with calendar that shows the relevant data assigned to Year and Month.

However on this page, I need a chart to show the full year so far (and as the months of this year continue, the chart expands with the year and month also. But if the Year selected is 2016 and the Month is May, I don't want this chart to show only 2016 and May, it should show the full year so far while the other data on the page does show 2016 and May.

I only want this one chart to show the full years data.

Any ideas?

Re: Show all even when date is selected

marcus_sommer — Fri, 13 May 2016 10:26:02 GMT

You could with set analysis ignore or overwrite selections - maybe something like this:

sum({< Month = >} Value)

will ignore selections in Month.

- Marcus

Re: Show all even when date is selected

sunny_talwar — Fri, 13 May 2016 10:26:03 GMT

May be use a set analysis to restrict the chart to show the year so far:

{<Date = {"$(='>=' & Date(YearStart(Today()), 'DateFieldFormatHere') & '<=' & Date(Today(), 'DateFieldFormatHere'))"}, Month, Year>}

UPDATE: Added the set analysis to ignore selection in Month and Year fields

Re: Show all even when date is selected

swuehl — Fri, 13 May 2016 10:27:09 GMT

You can use set analysis to change the scope of your aggregation,something like

=Sum({<DateField = {">=$(=YearStart(Max(DateField)))<=$(=Date(Max(DateField)))"}, MonthField= >} Value)

Re: Show all even when date is selected

swuehl — Fri, 13 May 2016 10:28:37 GMT

Re: Show all even when date is selected

bobbydave — Fri, 13 May 2016 12:32:40 GMT

Yes, this will show me a dimension of >=2016-Jan <= 2016-May

But I want it to show

2016-Jan 2016- Feb 2016-Mar 2016- Apr 2016-May

x x x x x

Re: Show all even when date is selected

sunny_talwar — Fri, 13 May 2016 12:35:29 GMT

Not sure I understand this

bobbydave wrote:

But I want it to show

2016-Jan 2016- Feb 2016-Mar 2016- Apr 2016-May

x x x x x

Re: Show all even when date is selected

bobbydave — Fri, 13 May 2016 12:43:59 GMT

This shows the values for 2016 March but assume its May (I haven't loaded April or May data yet so it hasn't picked it up). I am more concerned with the last value 0.86, 6.78 etc

So what I want to do now is create another chart to show previous months from the start of the year

so this would look like

Service 2016-Jan 2016-Feb 2016-Mar 2016-Apr 2016-May

ValueA 1.1 0.4 0.81 0.12 0.86

Re: Show all even when date is selected

swuehl — Fri, 13 May 2016 13:15:28 GMT

Just use the expression with the set analysis and an additional dimension (in addition to Service field dimension) like YearMonth to group your results.

You can use these two dimensions and the expression in a pivot table chart, then pivot the YearMonth dimension to the top.

If you don't have this YearMonth dimension field in your model, you can create a calculated dimension like

=MonthName(DateField)

Re: Show all even when date is selected

bobbydave — Mon, 16 May 2016 10:24:27 GMT

Find attached a dummy application of what I am trying to do.

When it shows whatever date I have selected, I don't want Average Speed table to change the date.

I want it still to show the full year even though a date is selected.

Each time I load new date for a new month, the Average Speed table will then add that new month on e.g for April and May, it would show Jan 2016, Feb 2016, March 2016, April and May and when a Year and Month is selected at the top, the Average Speed table would remain unchanged ie all months would still appear.

Re: Show all even when date is selected

sunny_talwar — Mon, 16 May 2016 11:14:00 GMT

Can you check the attached

Re: Show all even when date is selected

bobbydave — Mon, 16 May 2016 11:22:10 GMT

Hi Sunny,

I am definitely not explaining myself correctly.

I've removed the other tables and left the only table I need.

So right now I have selected 2016 March in the application.

In the Average Speed table, I want ALL of the Average Speed for 2016 to appear even though I have selected the dates 2016 March in the calendar.

The Average Speed should have the current years data appearing from Jan to today.

Re: Show all even when date is selected

sunny_talwar — Mon, 16 May 2016 11:29:21 GMT

Try now and see if this is what you want. I have created two tables, Average Speed 1 is always going to show current year's average speed no matter what year you select in your application. Average Speed 2 will change based on your selection in Year field (but will still show the complete year

Average Speed 1 Expression:

Num((Count({<group= {'AXA_LPI:Service_Desk',

'AXA_LPI:New_User_Setup',

'AXA_LPI:New_TIA_User_Setup',

'AXA_LPI:VTC_Requests',

'AXA_LPI:TIA_ROFC'}, Year = {$(=Year(Today()))}, Month, Date>} TV_Number)

)/(Num(Sum({<group= {'AXA_LPI:Service_Desk',

'AXA_LPI:New_User_Setup',

'AXA_LPI:New_TIA_User_Setup',

'AXA_LPI:VTC_Requests',

'AXA_LPI:TIA_ROFC'}, Year = {$(=Year(Today()))}, Month, Date>} TV_Open_Days), '#.##')

), '#.##')

Average Speed 2 Expression:

Num((Count({<group= {'AXA_LPI:Service_Desk',

'AXA_LPI:New_User_Setup',

'AXA_LPI:New_TIA_User_Setup',

'AXA_LPI:VTC_Requests',

'AXA_LPI:TIA_ROFC'}, Year = {$(=Max(Year))}, Month>} TV_Number)

)/(Num(Sum({<group= {'AXA_LPI:Service_Desk',

'AXA_LPI:New_User_Setup',

'AXA_LPI:New_TIA_User_Setup',

'AXA_LPI:VTC_Requests',

'AXA_LPI:TIA_ROFC'}, Year = {$(=Max(Year))}, Month>} TV_Open_Days), '#.##')

), '#.##')

Re: Show all even when date is selected

bobbydave — Mon, 16 May 2016 11:37:53 GMT

Hi Sunny,

Thanks so much.

Average Speed Two works perfect now once I add YearMonth in as a dimension.

This is what I was trying to do.

Thanks so much

Re: Show all even when date is selected

sunny_talwar — Mon, 16 May 2016 11:50:43 GMT

So is everything working now? If it is, I would request you to close this thread by marking correct and helpful responses.

Best,

Sunny