topic Re: Ignore filter on X axis for cumulative graph in QlikView

Ignore filter on X axis for cumulative graph

hhajjali — Sat, 13 Jan 2018 14:33:18 GMT

Hello Qlick community,

I need your help concerning my problem.

I am using Qlik sense.

I have 2 tables, a table which is my calendar reference and containing first day of each month. A second table containing 3 fields: KEY, CREATION DATE.

I have created a cumulative graph to have the sum off all created KEY each month:

DIM: = [Reference Date.autoCalendar.Year] & '-' & [Reference Date.autoCalendar.Month]

MESURE: = RangeSum(Above(COUNT(Distinct if([Reference Date.autoCalendar.YearMonth]=[CREATION DATE.autoCalendar.YearMonth] ,[KEY] )),0,RowNo(TOTAL)))

I obtain the bellow graph which so far OK:

My problem is the following, I want to limit the number of month I display to 6 months, I tried to filter the result in dimension, or using filter but it does not work, it ignore all entries before the 6 last months and the graph I obtain is the follow, so I losing 90 KEYs.

Thanks in advance.

Re: Ignore filter on X axis for cumulative graph

rubenmarin — Sat, 13 Jan 2018 17:47:12 GMT

Hi HAJ, one way to show cummulative months is using an AsOf calendar:

The As-Of Table

The bad thing is that it uses another field as dimensions but allows for a lot easier expressions.

Re: Ignore filter on X axis for cumulative graph

sunny_talwar — Sat, 13 Jan 2018 19:11:18 GMT

If you need expression only solution, you can try this

=RangeSum(Above(Count(DISTINCT {<Date>} If([Reference Date.autoCalendar.YearMonth]=[CREATION DATE.autoCalendar.YearMonth] ,[KEY] )),0,RowNo(TOTAL))) * Avg({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -5)) & '<=' & Date(Max(Date)))"}>} 1)

Re: Ignore filter on X axis for cumulative graph

hhajjali — Sat, 13 Jan 2018 20:48:49 GMT

Hello,

stalwar1 unfortunately your proposition does not work, by default it add more record to the original results, and by filtering only on the last 6 months I still don't have the correct result. By I really want to resole the issue by expression. Screen shots bellow.

rubenmarin‌ I appreciate your solution, but I have already multiple dimensions to work with and using the AS-OF table will add more complexity.

Graph after Sunny's formula:

What I want to see:

Other ideas guys?

Re: Ignore filter on X axis for cumulative graph

sunny_talwar — Sat, 13 Jan 2018 21:41:42 GMT

The image you got above using the expression i gave, was this after or before making selections? Would you be able to share your qvf to check it out?

Re: Ignore filter on X axis for cumulative graph

hhajjali — Sat, 13 Jan 2018 23:03:52 GMT

Hello Sunny,

Please find attached the .qvf. Data are a little bit different, it a test qvf.

The graph I posted was after the selection of last 6 months.

Thanks for your help.

Re: Ignore filter on X axis for cumulative graph

sunny_talwar — Sun, 14 Jan 2018 04:16:50 GMT

You always want to see last 6 months from today? Try this

RangeSum(

Above(

COUNT(Distinct

{<[Date Réference]>} if([Date Réference.autoCalendar.YearMonth]=[DATE CRÉATION.autoCalendar.YearMonth] ,[KEY] ))

,0,RowNo(TOTAL)))* Avg({<[Date Réference] = {"$(='>=' & Date(MonthStart(Today(), -5)) & '<=' & Date(Today()))"}>} 1)

Re: Ignore filter on X axis for cumulative graph

hhajjali — Sun, 14 Jan 2018 09:35:14 GMT

Thanks a lot for your help!

As extra solution, do you how I can get the same result but by filtering at what ever date segments ?

Re: Ignore filter on X axis for cumulative graph

sunny_talwar — Sun, 14 Jan 2018 16:26:45 GMT

Try this

RangeSum(

Above(

COUNT(Distinct

{<[Date Réference]>} if([Date Réference.autoCalendar.YearMonth]=[DATE CRÉATION.autoCalendar.YearMonth] ,[KEY] ))

,0,RowNo(TOTAL)))* Avg({<[Date Réference] = {"$(='>=' & Date(MonthStart(Max({<[Date Réference] = {'<=$(=Date(Today()))'}>}[Date Réference]), -5)) & '<=' & Date(Max({<[Date Réference] = {'<=$(=Date(Today()))'}>}[Date Réference])))"}>} 1)