pivot/direct tables having to calculate range

[#1]

Hi,

     I have imported an excel data into qlikview and trying to create a report with some ranges/slabs and how many counts are there for each of these ranges/slabs. You can see the sample data and the required report in the attached excel. The logic is also given along with the problem statement. Please let me know how this can be achieved.

Thanks,

Lax

[kaushiknsolanki]

Hi,

     Here is the expression.

     count({<[Branch Name] = {'=(sum({<Particular = {"New *"},Period={"Actual"},Month_Code={"12"}>}Amount)/sum({<Particular = {"New *"},Period={"Budget"},Month_Code={"12"}>}Amount))<=25'}>}DISTINCT [Branch Name])

     This expression is for 25% and you have to use the same for other percentages.

     Also find the attached sample file.

Regards,

Kaushik Solanki

[#3]

Thanks Kaushik for this quick one, 1 issue here is that for 50% if I use <= 50 then it will also give me the count of <=25. it should be > 25 and <=50. How do we achieve this in the above  set analysis ?

Thanks,

Lax

[kaushiknsolanki]

Hi,

    Then you can repeat the expression and do it like shown below.

count({<[Branch Name] =

{'=(sum({<Particular = {"New *"},Period={"Actual"},Month_Code={"12"}>}Amount)/sum({<Particular = {"New *"},Period={"Budget"},Month_Code={"12"}>}Amount))>=26 AND

(sum({<Particular = {"New *"},Period={"Actual"},Month_Code={"12"}>}Amount)/sum({<Particular = {"New *"},Period={"Budget"},Month_Code={"12"}>}Amount))<=50 '}

>}DISTINCT [Branch Name])

Regards,

Kaushik Solanki

[#5]

Hi,

     I created a variable and stored the common script there as follows. but it doesn't seems to work. Please have a look.

Variable is:

v_ActtoBud = sum({<Particular = {"New *"},Period={"Actual"},Month_Code={"12"}>}[Amount (Rs#)])/sum({<Particular = {"New *"},Period={"Budget"},Month_Code={"12"}>}[Amount (Rs#)]) *100

for 25% the set analysis is like this.

=count({<[T9 Code] = {'=$(v_ActtoBud)<=25'}>}DISTINCT [T9 Code] )

All I am getting is 0 as the output.

[#6]

Hi,

          With your above exp. I am not getting the exact output. I am also getting the branches who are having more than 50%.

count({<[Branch Name] =

{'=(sum({<Particular = {"New *"},Period={"Actual"},Month_Code={"12"}>}Amount)/sum({<Particular = {"New *"},Period={"Budget"},Month_Code={"12"}>}Amount))>=26 AND

(sum({<Particular = {"New *"},Period={"Actual"},Month_Code={"12"}>}Amount)/sum({<Particular = {"New *"},Period={"Budget"},Month_Code={"12"}>}Amount))<=50 '}

>}DISTINCT [Branch Name])

[kaushiknsolanki]

Hi,

     Can you send me the file which shows the branches above 50 %

Regards,

Kaushik Solanki

[#8]

HI, you can find the sample data along with the qlikview doc. I also tried to put the exp. in a variable but it doesn't seem to work.

Thanks for the help in advance.

Regards

Lax

[kaushiknsolanki]

Hi,

     Have a look at below example

Regards,

Kaushik Solanki

[#10]

Hi,

Sure, I will check it, just wanted to know if the common exp. can be put into a variable.

B'coz, as mentioned in one of my previous thread I was not getting the correct output or rather I was simply getting 0 as the output.

Thanks,

Lax