topic Re: Generate Country and State Numbers based on Region numbers in QlikView

Generate Country and State Numbers based on Region numbers

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

Hello all,

I have my data as in the Excel attached.

Overview:

Basically, i have a Month, StateID and RegionID corresponding to the State along with the Visit IDs and the Number of Products as KPIs.

Now, i want to use this KPIs(Visit ID and ProductSum) and generate 4 new columns.

Count of Visits per State
Sum of Products per State
Counts of Visits on Whole(i.e, Country)
Sum of Products on Whole(i.e, Country)

Please, see the attached Excel to get a more detail overview and expected result.

I want to do this at script level and store the values in a qvd.

NOTE:

Visits is always count and Products is always Sum.

Re: Generate Country and State Numbers based on Region numbers

devarasu07 — Wed, 29 Nov 2017 16:40:56 GMT

HI,

try like this

Total Visits Region

Count( total <[Region ID],Month> distinct [Visit ID])

Sum of Products Region

sum( total <[Region ID],Month> [Total Products])

Re: Generate Country and State Numbers based on Region numbers

devarasu07 — Wed, 29 Nov 2017 16:49:08 GMT

Hi,

refer to the attached output. it's similar to sql group by class with count & sum function

Total Visits Region

Count( total <[Region ID],Month> distinct [Visit ID])

Sum of Products Region

sum( total <[Region ID],Month> [Total Products])

Total Visits State

Count( total <[State ID],Month> distinct [Visit ID])

Sum of Products State

sum( total <[State ID],Month> [Total Products])

Total Visits Country

Count( total <Month> distinct [Visit ID])

Sum of Products Country

sum( total <Month> [Total Products])

Close your thread by marking as correct.

Thanks,

Deva

Re: Generate Country and State Numbers based on Region numbers

psk180590 — Thu, 30 Nov 2017 08:28:26 GMT

Thanks for the suggestion but, i want to generate this at the script level not at the application level using Set Analysis.

Re: Generate Country and State Numbers based on Region numbers

psk180590 — Thu, 30 Nov 2017 08:56:07 GMT

Hello All,

Anyone here faced a similar situation before hand?

Re: Generate Country and State Numbers based on Region numbers

PrashantSangle — Thu, 30 Nov 2017 09:13:20 GMT

For every expression you have to load the same table multiple time with respected granularity.

for example

1: If you want calculate sum of Products on Whole Month wise and Counts of Visits on Whole(i.e, Country)

then

Load Month,

Sum([Total Products]) as sum_of_products_month_wise,

count(Distinct [Visit ID]) as count_of_visit_id_whole_month_wise

from Data.xlsx

group by Month;

Regards,

Re: Generate Country and State Numbers based on Region numbers

manoj217 — Thu, 30 Nov 2017 09:49:05 GMT

Hi,

you want these function in script level only.Try this

Load Visit ID

Group by Region as Total_visits_Region,

Visit ID group by state as Total_visits_state,

Visit ID group by country as Total_visits_country,

sum(Total Products) group by Region as sum_of_total_products_region,

sum(Total Products ) group by state as sum_of_total_products_state,

sum(Total products) Group by country as sum_of_total_products_country

Resident (above table name);

Drop table (above table);

Re: Generate Country and State Numbers based on Region numbers

manoj217 — Thu, 30 Nov 2017 09:55:41 GMT

Load Visit ID

Group by Region as Total_visits_Region,

Visit ID group by state as Total_visits_state,count(Total_visits_state),

Visit ID group by country as Total_visits_country,count(Total_visits_country)

sum(Total Products) group by Region as sum_of_total_products_region,

sum(Total Products ) group by state as sum_of_total_products_state,

sum(Total products) Group by country as sum_of_total_products_country

Resident (above table name);

Drop table (above table);

Re: Generate Country and State Numbers based on Region numbers

psk180590 — Thu, 30 Nov 2017 10:30:55 GMT

Hello All,

Thanks for the suggestions. This is turning out to be a bit more complicated than it seems to be. I have made a sample app and also a few screen shots below to explain better.

For Ex:

In the month Jan i should see the following Numbers for those particular selections

Country - 10 Visits(Count OF Distinct Visit ID) and 12 Products(Sum of Total Products) - Irrespective of selection

State 1000 - 9 Visits and 9 Products - When State 1000 is selected

Region 1002 - 8 Visits and 9 Products - When Region 1002 is selected

But, if you see the application it gives me some completely different numbers.

The Country Numbers should not Respond if a State or Region is selected and the State numbers should not respond if a Region is selected.

Please, provide your valuable suggestions.