Previous Quarter in Set expression

[sarasmonu]

Hi All,

I am trying to get the Volume for previous quarter . If I select the filter as Q3-2017 i want the volume for Q2-2017.

Below is my set expression. By default it shows the previous quarter Volume, but when i select a quarter value from filter, the chart disspears and display a message "The selections generated no data for this chart"

Sum({$<[START TIME] = {">=$(=(Date(Addmonths(QuarterStart(Maxstring([START TIME])),-3),'YYYY-MM-DD'))) <=$(=(Date(Addmonths(QuarterEnd(Maxstring([START TIME])),-3),'YYYY-MM-DD')))"} >}VOLUME_IV)

let me know what am i missing.

Please help.

Thanks

[rubenmarin]

Hi Monu, the expression shows values for last quarter, when you a select a quarter ther is no value wich is in both conditions: this quarter (value selected) and previous quarter (set analysis).

You need to ignore selections in the quarter field, ie:

Sum({$<[START TIME] = {">=$(=(Date(Addmonths(QuarterStart(Maxstring([START TIME])),-3),'YYYY-MM-DD'))) <=$(=(Date(Addmonths(QuarterEnd(Maxstring([START TIME])),-3),'YYYY-MM-DD')))"}, QUARTER_FIELD=>}VOLUME_IV)

You probably need to add more fields to ignore in set analysis (month, week,..).

[sarasmonu]

Hi Rubin,

I am expecting it to display the previous Quarter value of whatever is selected in the Quarter field.

Thanks

[sarasmonu]

Just to add, for example if i select Q2-2017, i want the  chart to display Q1-2017.

[rubenmarin]

I know, I was talking about that, read again and ask any doubts you have.

The main issue is that you have to ignore selections in Quarter field in set analysis.

[sarasmonu]

Thank you so much Rubin.This works!

[sarasmonu]

Hi Rubin,

Thanks for ur help for my previous post.

I have another quick question to ask. This is regarding color by expression,

when i have 2 or more value that should have the same color, it does not work.

Below is an example:

Pie chart dimension is as follows:

=If(PRIORITY='Very High' OR PRIORITY='High, 'High', 'Low')

I am using the same expression for "color by expresssion" as:

=If(PRIORITY='Very High' OR PRIORITY='High', 'Red', 'Blue')

But this doesn't seem to work. If you could please help me with this.

Thanks

[rubenmarin]

Hi Manu, that's not the way to assign colors.

There are functions like Red() or Blue() wich returns predefined colors, also there are other funcions like RGB() or ARGB() to assign specific colors:

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/ColorFunctions/co...

This expression can be:

=If(PRIORITY='Very High' OR PRIORITY='High', Red(), Blue())

Also, priority has to have only one possible value to assign the color, if it's grouped you will need an aggregation funcion that returns only one value, ie:

If(MaxString(PRIORITY)='Very High' OR MaxString(PRIORITY)='High', Red(), Blue())

[MK9885]

Create a Flag for Previous Quarter and use that in front end.

Below are the various flags created in Master Calendar script.

if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,
if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,
if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,
if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,
if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,
if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,
if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,

Your eexpression might be Sum({$< {[START TIME], LastMnthPerQtr={1}>+<[START TIME],LastMonthFlag={1}} >}VOLUME_IV)

I guess that should be it...

[MadhuriReddy]

Hi Ruben,

Thanks for the solution. This idea works for my scenario also.