topic Re: Calculate sum for selected year in QlikView

Calculate sum for selected year

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I need help on this if anyone knows.

Below are my QlikView file and for the questions:

1 Calculate total revenue of all stores for Year 2007? (question 3 in Qlik)

2. Year 2006: Region Missouri: what is the total revenue - divided also on the stores? (question 5 in Qlik)

I tried to use set analysis to find the total but It did not show anything on my file.

Thanks.

Re: Calculate sum for selected year

swuehl — Sat, 20 Oct 2018 19:56:32 GMT

=Sum({1<Year = {2007} >}Revenue)

Don't understand your question (divided also on the stores)? Do you want to use a Store as dimension?

=Sum({1<Year = {2006}, Region = {'Missouri'} >} Revenue)

The {1<... part ignores any user selections and uses the field modifiers as written. If you want to not ignore the user selections other than modified using the field modifiers for Year and Region, use {$<.. instead

A Primer on Set Analysis

Re: Calculate sum for selected year

Anonymous — Sat, 20 Oct 2018 20:14:04 GMT

Hi, Thanks a lot to your answer. "Divided to stores" - I think here question wants to answer the revenue of all store as well as know how much each store get.

Also, where do I write the code you give me, in the Edit script , or in Properties- Set expression?

Re: Calculate sum for selected year

Anonymous — Sat, 20 Oct 2018 20:18:13 GMT

Seems like I didn't get what I want. As I want the total revenue of eg. store 1+2+3 = ... Here it only shows store 1 = ..., store 2 = ...

Re: Calculate sum for selected year

swuehl — Mon, 22 Oct 2018 17:11:39 GMT

For 1) I assumed you need a single value, e.g. using my expression in a text box.

If you want the total Revenue in a chart's dimensional line, you may need to apply the TOTAL qualifier:

=Sum({1<Year = {2007} >} TOTAL Revenue)

The questions are pretty unclear to me. Particularly, in which context (which chart type with what dimensions) you want to calculate the answer.