topic Re: Set Analysis with variables in QlikView

Set Analysis with variables

matancha — Mon, 29 Apr 2013 07:26:38 GMT

Hello all.

I'm want to show sales in the same period (of selection) of 12 months back

I hold 2 variables:

vDateMin = MonthStart(AddMonths(min(Date), -12))

vDateMax = MonthEnd(AddMonths(Max(Date), -12))

And now I want to use the variable in pivot table expression like: Sum({$<Date = {"> $(#vDateMin) < $(#vDateMax)"}>}$(vSales))

and It doesn't work for me.

if I do it manually like this: Sum({$<Date = {">=01/01/2011 <= 28/02/2012"}>}$(vSales)) Its work fine.

What am I doing wrong?

Tnks -

Matan.

Re: Set Analysis with variables

fdelacal — Mon, 29 Apr 2013 08:10:08 GMT

Hi plzmatanch

Sum({$<Date = {"$(='>'&(vDateMin)&'<'(vDateMax)"}>}$(vSales))

please trie this way.

hope that helps you.

Re: Set Analysis with variables

jagan — Mon, 29 Apr 2013 08:43:14 GMT

Hi,

Try this expression

vDateMin = MonthStart(AddMonths(Max(Date), -12))

vDateMax = MonthEnd(AddMonths(Max(Date), -12))

=Sum({<YearDimensionName=, MonthDimensionName=, QuarterDimensionName=, Date = {">=$(=vDateMin)<=$(=vDateMax)"}>}$(vSales))

Exclude the date filters like Year, Month or Quarter selections if there are any. Also check whether the date formats are same in Variable and Dimension.

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis with variables

its_anandrjs — Mon, 29 Apr 2013 09:28:33 GMT

Hi,

Please check your date format of the variable Date .

Regards,

Anand

Re: Set Analysis with variables

Mon, 29 Apr 2013 10:07:56 GMT

your script working fine.

try this

=Sum({$<Date = {'> $(=vDateMin) <$(=vDateMax)'}>}$(vSales))

Re: Set Analysis with variables

matancha — Mon, 29 Apr 2013 10:30:46 GMT

Tnks jagan.

I Think that my problem was that I didn't do the "Year=...."

many thanks to all of you for your answers -

Matan.

Re: Set Analysis with variables

vinod22kv — Thu, 05 Oct 2017 08:55:25 GMT

Hi,

can i have some simple example on this please.

Regards,

Vinod