Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

antonrus
New Contributor III

Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

Hello Help please! Need to calculate the sum of the week for the current year (eg 2014) but provided that if for that same week had data for the last year (2013). If data for the same week last year was not - sale this week should not be exposed to the sum. And painfully hard ...

1 Solution

Accepted Solutions
Not applicable

Re: Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

It is not possible because I have the Personal Edition (I would ahve attached it and done the test with your data)

Sorry for that

Picture 1

A pivot table with the corresponding syntax

A pivot table with Sum(Amount) to show the stucture

Picture 2

The first pivot table, I have added Sum(Amount) to show that the syntax returns 0 in that case

I cannot attach your qvw, you will be able to attach mine. That is the last sheet.

Fabrice

13 Replies
datanibbler
Esteemed Contributor

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

Hi,

in that case, when you do NOT have any sales_data for a specific week last year - will there be a 0 or just nothing?

=> Depending on that, you have to build your query.

With set_analysis, it's actually not that hard.

Best regards,

DataNibbler

antonrus
New Contributor III

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

(I do not speak in English, and could not quite understand). In the table, if there are no sales (Summ) then there is no record on the week against.

Probably using set_analysis it is not difficult to do but I can not for several days of heavy ..

datanibbler
Esteemed Contributor

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).


Hi,

just tried it out:

=> In this case, a simple IF()_construct should be sufficient:

=> Change your formula for 2014 to
     >>> IF(SUM ({<year={'2013'}>} Summ)  <> 0, SUM ({<year={'2014'}>} Summ)) <<<

==>> That way, your sales_data for 2014 will only be displayed in case the sales_data for 2013 is <>0.

HTH

Best regards,

DataNibbler

antonrus
New Contributor III

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

RestNum IF(SUM ({<year={'2013'}>} Summ)  > 0, SUM ({<year={'2014'}>} Summ)) 20132014
55980149 113125 871149 113
5628482 243229 57582 243
56525105 941236 492105 941
59045-038 485
60385-077 555
453 337591 938453 337

Thank you. These really are not displayed if in 2013 there were no sales! But the amount is still considered full. And should that be not considered. Help please! ..

Not applicable

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

Ahtoh,

The result for each RestNum seems to be correct.

* If you have 0 in 2013, you get a Null() result: the dash: -

* If you have a result > 0, you have the 2014 data

The aggregation is not correct because you get the 2014 sum.

Try to do : sum(aggr(YourCurrentExpressionWithTheIF, RestNum))

Fabrice

antonrus
New Contributor III

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

I feel that you are right! But with such a formula he believes all the data again. I do not understand ..: (

Not applicable

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

I will do a test

antonrus
New Contributor III

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

I have no words as you will be grateful!

Not applicable

Re: Calculate the sum of the week for the current year (eg 2014), but on the condition that if in this same week data were last year (2013).

Ahtoh,

On a db of mine, I have done that:

  sum({<Customer={"=sum({<Month={3}>} Amount)>0"}>} Amount)

I get only the customers where the Amount for the 3d month > 0. I have a good total. There is a set (sum > 0) into a set (on customer that I want to limit).

For you, if I transpose to your db, try sth like:

sum({<RestNum={"=sum({<Year={2013}>}, Summ) > 0"}>} Summ)

Fabrice

Community Browser