topic Re: Set Analysis Addition in QlikView

Set Analysis Addition

tim_at_ford — Sat, 16 Nov 2024 21:20:49 GMT

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.

Re: Set Analysis Addition

tincholiver — Tue, 26 Feb 2019 19:05:38 GMT

Can you share some data for help you?

Re: Set Analysis Addition

dplr-rn — Tue, 26 Feb 2019 19:36:05 GMT

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

Re: Set Analysis Addition

tincholiver — Tue, 26 Feb 2019 19:51:21 GMT

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,

Re: Set Analysis Addition

sunny_talwar — Tue, 26 Feb 2019 20:50:54 GMT

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

Re: Set Analysis Addition

tim_at_ford — Tue, 05 Mar 2019 14:18:10 GMT

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?

Re: Set Analysis Addition

tim_at_ford — Tue, 05 Mar 2019 18:23:45 GMT

Sunny

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

Tim