Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mor_rave
New Contributor

How to show the last 3 months of data on a bar chart

I created the measure : Sum(Aggr(Count(DISTINCT {<KPI_DESC = {'Data Accuracy'}>} Routine), DR_GID, Country))


I am trying to show the last 3 months of the measure above on a bar chart when selecting a specific month (example:  Select June 2017 and display April, May & June 2017 data).  I also need to be able to further filter that data on other filters .


my data bases is :

       

DR_TIME_KEY

DR_YEAR

DR_QUARTER

DR_MONTH

DR_GID

KPI_DESC

Country

2017113020174112007654Data AccuracyDenmark
2017103020174101954984Data AccuracyDenmark
201712302017412193777Data AccuracySwitzerland
20170830201738194265Data AccuracySwitzerland
20170730201727883289Data AccuracySwitzerland
201706302017261952569Data AccuracyUnited States
201711302017411193894Data ConsistencySwitzerland
201711302017411914884Data ConsistencySwitzerland
2017113020174111954527Data AccuracyUnited States
201711302017411887302Data AccuracySwitzerland
201711302017411194285Data ConsistencySwitzerland
201711302017411914902Data ConsistencySwitzerland

I want to know what is supposed to be the new measure and the chart dimensions

if I want to choose every time different year/month and present the last 3 months which field I need to choose as a filter? 

Any help would be appreciated

8 Replies
omarbensalem
Esteemed Contributor

Re: How to show the last 3 months of data on a bar chart


try


Sum(Aggr(Count(DISTINCT {<KPI_DESC = {'Data Accuracy'}, DR_MONTH={">= $(=max(DR_MONTH)-3) <=$(=max(DR_MONTH))"} >} Routine), DR_GID, Country))



or


make sure ur DR_TIME_KEY field is a date; change it in ur script as follow:

date(date#(DR_TIME_KEY ,'YYYYMMDD')) as DR_TIME_KEY

then:


Sum(Aggr(Count(DISTINCT {<KPI_DESC = {'Data Accuracy'},

DR_TIME_KEY={">= $(=AddMonths(max(DR_TIME_KEY)),-3)) <= $(=max(DR_TIME_KEY))"} >} Routine), DR_GID, Country))

MVP
MVP

Re: How to show the last 3 months of data on a bar chart

I would use set analysis on the date field to restrict the chart to show the 3 latest months

Dates in Set Analysis

In your sample, how do you create your DR_TIME_KEY field? Is this created in the script or brought it directly from data base? Is this a date field or a number field?

mor_rave
New Contributor

Re: How to show the last 3 months of data on a bar chart

Hi Sunny,

I've tried Omar solution but it still doesn't work.

the DR_TIME_KEY field is  brought it directly from data base, I think it is a dta field brcausr the Qlik created manually Year, Month etc' for this field. ( I attached screenshot)

hope you can help me with my question.

MVP
MVP

Re: How to show the last 3 months of data on a bar chart

Can you tried his modified suggestion (after or)

mor_rave
New Contributor

Re: How to show the last 3 months of data on a bar chart

Hi,

I've tried his suggestion but it doesn't work. I got 0 as a results per each month.

I attached the QVF maybe I did something wrong.

MVP
MVP

Re: How to show the last 3 months of data on a bar chart

Try this

Sum({<KPI_DESC = {'Data Accuracy'}, DR_TIME_KEY = {"$(='>=' & AddMonths(max(DR_TIME_KEY),-3) & '<=' & max(DR_TIME_KEY))"}, [DR_TIME_KEY.autoCalendar.Month]>}

Aggr(Count(DISTINCT {<KPI_DESC = {'Data Accuracy'}, DR_TIME_KEY = {"$(='>=' & AddMonths(max(DR_TIME_KEY),-3) & '<=' & max(DR_TIME_KEY))"}, [DR_TIME_KEY.autoCalendar.Month]>} Routine), DR_GID, Country))

For November, there was no data for August, September, & October... but when I select July... it seems to work

Capture.PNG

mor_rave
New Contributor

Re: How to show the last 3 months of data on a bar chart

WOW...... Thanks it's working!!!!! I never know how to write it like you.

Do you know if there is any guidebook or website for setanalysis?

MVP
MVP

Re: How to show the last 3 months of data on a bar chart

Try the document attached here

Set Analysis: syntaxes, examples