Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Hide dimensions who are used in acc sum

Hi there,

I used the folowing expression to accumulate a expression 12 yeamonth values back

=rangesum(above( sum({<CalenderYearMonth={"<=$(=max(Year)&right(0&max(Month),2))"}>}#SalesInvoiceQuantityInKilo),0,12) )

Is it possible to hide the first 12 dimension values.

The set analysis is used to select my calendar with is not connected becasue i have a fiscal and a normal calendar.

Who can help me with this question?

Regards,

Dirk

1 Solution

Accepted Solutions

Re: Hide dimensions who are used in acc sum

Attaching a sample for you. May be this will explain what I am trying to do:

Capture.PNG

Expression 1: =RangeSum(Above(Sum({<Date = {"$(='<=' & Date(Today(), 'M/DD/YYYY'))"}>}Value), 0, 12))

This one is same as yours.

Expression 2: Sum({<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY'))"}>}Value)

Forced 0 for Months we don't want to see on the line chart

Expression 3:

=If(Sum({<Date = {"$(='>=' & Date(AddYears(Today(), -1), 'M/DD/YYYY'))"}>}Value) > 0,

RangeSum(Above(Sum({<Date = {"$(='<=' & Date(Today(), 'M/DD/YYYY'))"}>}Value), 0, 12)))

This is what you need.

Using the third expression in the line chart, I get this:

Capture.PNG

I hope this will help.

Best,

Sunny

16 Replies
Not applicable

Re: Hide dimensions who are used in acc sum

Please explain the question with sample data & qvw file

Re: Hide dimensions who are used in acc sum

Not sure if I completely understand what you want, but may be try this and see if this is what you intended for?

=If(Sum(#SalesInvoiceQuantityInKilo) > 0, RangeSum(Above(Sum({<CalenderYearMonth={"<=$(=max(Year)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo),0,12)))

Not applicable

Re: Hide dimensions who are used in acc sum

BTW, If you want Hide total dimension values, go to Chart Properties --> Presentation --> Select Dimension --> Select the Hide

Re: Hide dimensions who are used in acc sum

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations


talk is cheap, supply exceeds demand
Not applicable

Re: Hide dimensions who are used in acc sum

Because it a acc sum the first 12 values are above 0 but not totaly acc for 12 months.

I only want to see te months who can look 12 months back

Re: Hide dimensions who are used in acc sum

Try this:

=If(RowNo() <= 12, RangeSum(Above(Sum({<CalenderYearMonth={"<=$(=max(Year)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo),0,12)))

Not applicable

Re: Hide dimensions who are used in acc sum

That doesn't work because it still shows the dimms who are used for te calculation

Re: Hide dimensions who are used in acc sum

What about this


=If(Sum({<CalenderYearMonth={">$(=(max(Year)-1)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo) > 0, RangeSum(Above(Sum({<CalenderYearMonth={"<=$(=max(Year)&Right(0&max(Month),2))"}>} #SalesInvoiceQuantityInKilo),0,12)))



Note: =(max(Year)-1)&Right(0&max(Month),2) in a text box object should give you the date from which (not including) you want to see in your dimension value in the chart.

HTH

Best,

Sunny

Not applicable

Re: Hide dimensions who are used in acc sum

example.png

This is an example of te problem.

The first 12 months are used to accumalate the 13 value and i want only to see the 13 value and higher

Community Browser