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 — Mon, 08 Jan 2018 20:20:31 GMT

Hi Guys,

I am trying to show 6 months data on the report by writing set analysis using the "Add Calculated Dimension". Can someone please help me with the expression in the dimension or suggest any other option?

Current Date - Dec 2017. I want to show all dates from June. If I select November 2017, I would like to see all the dates from May 2017.

Expression I have been trying:

=Aggr(Only({$<Date={">=$(=date(max(Date),'MMM YYYY'))<=$(=Date(AddMonths(max(Date),-6),'MMM YYYY'))"} >} Date),Date)

Thanks in advance!

Cheers,

Varun

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

sunny_talwar — Mon, 08 Jan 2018 20:21:40 GMT

May be try with 1 instead of $

=Aggr(Only({1<Date={">=$(=date(max(Date),'MMM YYYY'))<=$(=Date(AddMonths(max(Date),-6),'MMM YYYY'))"} >} Date), Date)

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

varunreddy — Mon, 08 Jan 2018 20:26:10 GMT

Hi Sunny,

This is not working.

I also tried another method. I am adding each month in the expression.

Dimension - GB/GF Name

Current Month Expression - aggr(Count({<Date = {'$(vCurMonthYear)'}, [Key Business Driver] = {'BCBS239'}>} [DQ Rule Name]),[GB/GF Name])

Previous Month expression - aggr(Count({<Date = {"=Date(AddMonths(max(Date),-1),'MMM YYYY')"}>}[DQ Rule Name]),[GB/GF Name])

Previous Month expression - aggr(Count({<Date = {"=Date(AddMonths(max(Date),-2),'MMM YYYY')"}>}[DQ Rule Name]),[GB/GF Name])

This is also not working. Not sure I am missing something. Can you please help?

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

sunny_talwar — Mon, 08 Jan 2018 20:28:32 GMT

Why are you using Aggr() function if you have the same as your dimension

Try this

Dimension

[GB/GF Name]

Expressions

Count({<Date = {'$(vCurMonthYear)'}, [Key Business Driver] = {'BCBS239'}>} [DQ Rule Name])

Count({<Date = {"=Date(AddMonths(max(Date),-1),'MMM YYYY')"}>}[DQ Rule Name])

Count({<Date = {"=Date(AddMonths(max(Date),-2),'MMM YYYY')"}>}[DQ Rule Name])

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

varunreddy — Mon, 08 Jan 2018 20:32:56 GMT

Oh Yeah! Thanks for correcting. I tried it but I am seeing the same numbers for all the months, but this is not true

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

sunny_talwar — Mon, 08 Jan 2018 20:35:09 GMT

I am not seeing anything at all lol... I mean can you share a sample or image of what you are seeing... I won't know what you have until you show me something

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

varunreddy — Mon, 08 Jan 2018 20:44:27 GMT

It worked.

I created the 6 separate variables and calculated the dates. I referred those variables in the expression instead of calculating in the set analysis.

I am not sure why , but it is working.

My question here is performance wise, is it good to use it using the variable or is it better calculating in the set analysis?

Thanks for your help

Cheers,

Varun

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

sunny_talwar — Mon, 08 Jan 2018 20:46:33 GMT

Should be the same thing ...

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

varunreddy — Mon, 08 Jan 2018 20:48:12 GMT

Cool

Thanks,

Varun

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

varunreddy — Tue, 09 Jan 2018 16:46:25 GMT

Hi Sunny,

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 you please help?

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

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

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

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

Are you making selection in Date field? If you are, have you ignore selection in Date field from your expression?

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

varunreddy — Wed, 10 Jan 2018 19:38:21 GMT

Hi Sunny,

I have attached the source file and QVW file to the actual discussion. Please take a look and advise what I am missing.

My requirement: When I select a Date, I want to see past 6 months data.

Cheers,

Varun

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

sunny_talwar — Wed, 10 Jan 2018 19:46:13 GMT

Two things

1) Create your MonthYear field like this rather than using MonthName function. MonthName creates a string which makes it unusable in the set analysis....

Date(MonthStart(Date), 'MMM YYYY') as Date,

2) I asked you initially also that don't add spaces between >= and $() or <= or $()... just don't add any spaces in your set modifier

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

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

varunreddy — Wed, 10 Jan 2018 21:04:18 GMT

Hi Sunny,

I got my mistake. Will use Monthstart going forward instead of MonthName function.

Many Thanks,

Varun