topic Re: How to show 6 months data using the set analysis in the dimension in QlikView

How to show 6 months data using the set analysis in the dimension

varunreddy — Tue, 09 Jan 2018 16:55:58 GMT

Hi All,

I am trying to create line chart. Two dimensions and one expression.

Dimensions:

Date and GB/GF

Expression

Count(Rule Name)

This is working fine.

Now I am trying to show only 6 months of data out of all dates available. I am using below expression in the dimension to display 6 months data but I am not seeing the dates. Can someone please help?

=Aggr(Only({<Date = {'>= $(=vPrevious5MonthYear) <= $(=vCurMonthYear)'}>}Date),Date)

Below is the graph. I don't see Dates.

Cheers,

Varun

Re: How to show 6 months data using the set analysis in the dimension

sunny_talwar — Tue, 09 Jan 2018 17:24:05 GMT

Check my response here

How to show 6 months data using the set analysis in the dimension

Re: How to show 6 months data using the set analysis in the dimension

varunreddy — Tue, 09 Jan 2018 17:26:08 GMT

It actually didn't work.

Re: How to show 6 months data using the set analysis in the dimension

sunny_talwar — Tue, 09 Jan 2018 17:27:19 GMT

Share a sample if you can to show what you have (in addition to your dimension)

Re: How to show 6 months data using the set analysis in the dimension

CarlosAMonroy — Tue, 09 Jan 2018 17:52:11 GMT

Hi Varun,

You can try the following expression:

count(DISTINCT {< Date= {">=$(vMinDate) <=$(vMaxDate) "} >}Rule Name)

Where:

vMinDate = =Date(AddMonths(date(today()),-6))

vMaxDate = =Date(today())

Hope that helps,

Carlos M

Re: How to show 6 months data using the set analysis in the dimension

varunreddy — Tue, 09 Jan 2018 17:57:30 GMT

Hi Carlos,

It didn't work.

Dimensions used:

Date

GB/GF Name

Expression Used:

count({<Date= {">=$(vPrevious5MonthYear) <=$(vCurMonthYear)"} >}[DQ Rule Name])

vCurMonthYear = =date(max(Date),'MMM YYYY')

vPrevious5MonthYear = =Date(AddMonths(max(Date),-5),'MMM YYYY') .

Can you please see, if I am missing anything?

I want to see Current 6 months in the line chart by default, If I select any date, I want to see 6 months from the date selected.

Re: How to show 6 months data using the set analysis in the dimension

sunny_talwar — Tue, 09 Jan 2018 18:00:01 GMT

Not sure if this will change anything, but can you remove space between the end of variable name and <= (like below)

Count({<Date= {">=$(vPrevious5MonthYear)<=$(vCurMonthYear)"}>}[DQ Rule Name])

Re: How to show 6 months data using the set analysis in the dimension

varunreddy — Tue, 09 Jan 2018 18:06:31 GMT

Tried. No luck. Will see if I can mock up something and share.

Now I see this:

Re: How to show 6 months data using the set analysis in the dimension

CarlosAMonroy — Tue, 09 Jan 2018 18:13:11 GMT

Hey Varun,

Does the Date field have JAN 2018 as format?

Do you have any Date field using regular date format (MM/DD/YYYY).

I guess the issue is not the expression but the date format.

Try using different date fields.

Carlos M

Re: How to show 6 months data using the set analysis in the dimension

varunreddy — Tue, 09 Jan 2018 18:16:22 GMT

When I try to compute using any one variable, it works. i.e.count( {< Date= {'$(vPrevious5MonthYear)'} >}[DQ Rule Name]).

It is not working only when we are using both.

When I try max(Date), I am getting number. So I am trying to convert using Date format i.e. Date(Date,'MMM YYYY')

Re: How to show 6 months data using the set analysis in the dimension

CarlosAMonroy — Tue, 09 Jan 2018 18:24:51 GMT

Ok, but Date comes as JAN 2018 or 9/01/2018. Makes sense that you want to convert it, but maybe is not the proper format.