QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
New Contributor III

To whoever can help me!!

I want to do a YOY analysis for my KPI.  I have five years of data.  I want to subtract out the counts of 01/01/2018 thru 08/29/2018 against 01/01/2017 thru 08/28/2017, a YOY.

Now I know this algorithm is wrong (being new to Qlik View Set Analysis)

=Num(Count({<Salesdate={"<=\$(vCurrentDate)"}>}indictor) - count({<Salesdate={"<=01/01/2018"}>}indicator)
- Count({<Salesdate={"<=\$(vPrevDate)"}>}indicator)    - count({<Salesdate={"<=01/01/2017"}>}indicator), '###,##0')

However, individual everything for each year works fine!  I get my 2018 and 2017 counts individuals so I know I'm doing my set analysis correctly.

Where I run into issues is how to perform an arithmetic operation on both sets.  I know I'm supposed to get a negative number.  My first set has 900 records (2018) and my second set has 2,300 (2017).   I should get a negative number!!

Am I using the correct functions?   I have review a lot of material and it all appears to be just  comparing year to year not YOY.

Labels (4)

6 Replies
Contributor II

Re: Set Analysis Addition

Can you share some data for help you?
Partner

Re: Set Analysis Addition

Think you may have made a small mistake; you used '<= 01/01/2017' rather than >=

=Num(Count({<Salesdate={"<=\$(vCurrentDate)"}>}indictor) - count({<Salesdate={">=01/01/2018"}>}indicator)
- Count({<Salesdate={"<=\$(vPrevDate)"}>}indicator)    - count({<Salesdate={">=01/01/2017"}>}indicator), '###,##0')

Contributor II

Re: Set Analysis Addition

You should also put dates from up to each count, if you use Count ({<Salesdate = {"<= \$ (vCurrentDate)"}>} indictor) this account will bring data from the previous year which you do not need.
you should express something like the following

= Num (

(Count ({<Salesdate = {"> = 01/01/2018 <= \$ (vCurrentDate)"}>} indictor)

-

count ({<Salesdate = {"> = 01/01/2018 <= \$ ( vCurrentDate) "}>} indicator))

-

(Count ({<Salesdate = {"> = 01/01/2017 <= \$ (vPrevDate)"}>} indicator)

-

count ({<Salesdate = {"> = 01/01/2017 <= \$ (vPrevDate) "}>} indicator))

, '###, ## 0')

that way I will only tell you the data contained in that range,

MVP

Re: Set Analysis Addition

What exactly goes wrong when you use the combined expression? See an incorrect number? See null? If you see null, you can try this

```=Num(
RangeSum(
Count({<Salesdate={"<=\$(vCurrentDate)"}>}indictor),
-Count({<Salesdate={"<=01/01/2018"}>}indicator),
-Count({<Salesdate={"<=\$(vPrevDate)"}>}indicator)
-Count({<Salesdate={"<=01/01/2017"}>}indicator)
)
, '###,##0')```

Or may be this (changed the sign for the last count from - to +

```=Num(
RangeSum(
Count({<Salesdate={"<=\$(vCurrentDate)"}>}indictor),
-Count({<Salesdate={"<=01/01/2018"}>}indicator),
-Count({<Salesdate={"<=\$(vPrevDate)"}>}indicator)
+Count({<Salesdate={"<=01/01/2017"}>}indicator)
)
, '###,##0')```
New Contributor III

Re: Set Analysis Addition

Sunny

This issue has been resolved for arithmetic of the sets.  However, I've got another one now involving the two hard coded January dates, which I need, 01/01/2018 and 01/01/2017!!

In my set analysis statement for either {<Salesdate={"<=01/01/2017"}>}

I want to make the 01/01 a constant and then the 2017 variable.

For example:

My current date processing is such:

08/29/2018 thru 01/01/2018

vs.

08/29/2017 thru 01/01/2017

Now I want to make both 01/01 (MM/DD) constant and the year variable, conceptual example

{<Salesdate={"<=(variable one with 01/01)(variable two 2017 or whatever year I am comparing to)"}>}

How do I do that?

New Contributor III

Re: Set Analysis Addition

Sunny

Never mind!! I figured out the 01/01/Year or Year selected issue.

Tim