Solved: Hide dimensions who are used in acc sum - Qlik Community

Report Inappropriate Content · ‎2015-07-02

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

sunny_talwar · ‎2015-07-02

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

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:

I hope this will help.

Best,

Sunny

View solution in original post

Report Inappropriate Content · ‎2015-07-02

Please explain the question with sample data & qvw file

sunny_talwar · ‎2015-07-02

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)))

Report Inappropriate Content · ‎2015-07-02

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

Gysbert_Wassenaar · ‎2015-07-02

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

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2015-07-02

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

sunny_talwar · ‎2015-07-02

Try this:

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

Report Inappropriate Content · ‎2015-07-02

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

sunny_talwar · ‎2015-07-02

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

Report Inappropriate Content · ‎2015-07-02

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