topic Re: Weekend counts in QlikView

Weekend counts

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

HI All ,

I have to show the total counts by Weekend till today even if there is no date for that .

What I mean is I have a field call "Date" from which I am getting Weekend and also using the master calneder to generate the missing dates . But i am not able to generate it till today .

Say for example i have 2 counts on weekend 12-October 2019 and the next count is 5 on 30-Nov-2019 then my Count should show as 2 from 12-Oct-2019 till 23-Nov-2019. Which I am not able to get this . Can please someone help me on this ?

I am attaching my QVW file for refrence .

Re: Weekend counts

smilingjohn — Thu, 16 Jul 2020 10:32:24 GMT

Hi All

Any help please

Re: Weekend counts

smilingjohn — Thu, 16 Jul 2020 13:42:49 GMT

Hi Community

Any suggestions .

Thanks in Advance

Re: Weekend counts

Kushal_Chawda — Thu, 16 Jul 2020 13:59:04 GMT

try below. Column(1) means your first expression. You can change it accordingly

=sum(aggr(if(count({<Weekend>}ID)=0, above(total column(1)),count({<Weekend>}ID)),Weekend))*avg(1)

Re: Weekend counts

smilingjohn — Thu, 16 Jul 2020 14:23:44 GMT

Thanks for the reply Khush,

Now i see it giving the desired output , What do u mean by above(total column(1)), ?

As i copied your expression and pasted in mine and it worked .

But it is not showing the weekend date till today . The last daet is till March-7-2020 but i need to show it till Tdays date .How can I achieve this ?

Re: Weekend counts

Kushal_Chawda — Thu, 16 Jul 2020 16:07:59 GMT

Column(1) refers to the expression number of your chart. Let's say you have straight table and have two expressions. Then column(1) refers to first expression and column(2) refers to second expression.

If you want to create Dates till today, Can't you create it using master calendar?

Re: Weekend counts

smilingjohn — Thu, 16 Jul 2020 16:22:33 GMT

Thanks Khush

I have the master calendar created from date field .

But it is not giving me the data till date . How can I handle this .

The actual date in the data is till 7-march-2020

And I see the master calendar generating all the missing dates till 7march .

But I want it till today .how do I achieve this .

Thanks

Re: Weekend counts

Kushal_Chawda — Thu, 16 Jul 2020 16:36:53 GMT

can you share calendar script?

Re: Weekend counts

smilingjohn — Thu, 16 Jul 2020 16:53:49 GMT

Hi Khush,

Below is the script

Trend:
LOAD ID,
Status,
Date

FROM
[C:\trend.xlsx]
(ooxml, embedded labels, table is Sheet1);

Calendar:
LOAD

Date(MinDate + IterNo() -1,'DD/MM/YYYY') as Date,
WeekEnd(date(MinDate + IterNo() -1,'DD/MM/YYYY')) as Weekend,
Date(MinDate + IterNo() -1,'YYYY') as Year,
Date(MinDate + IterNo() -1,'MM/YY') as Month,
Date(MinDate + IterNo() -1,'YY') & '_' & Num(Week(MinDate + IterNo() -1),'00') as Week

While
MinDate + IterNo() -1 <=MaxDate;

LOAD
min(Date) as MinDate,
max(Date) as MaxDate

Resident Trend;

Re: Weekend counts

smilingjohn — Thu, 16 Jul 2020 17:13:44 GMT

Hi Khush,

This is the date i have Min date is 12-Oct-2019 and Max date is 4-Mar-2020 .The caledar is generating the dates from 12-Oct-2019 till 4-Mar-2020 but i want the dates to be till today. Thanks

Date

12-Oct-19

24-Nov-19

2-Dec-19

12-Jan-20

4-Mar-20

Re: Weekend counts

Kushal_Chawda — Thu, 16 Jul 2020 17:33:58 GMT

try this

Trend:
LOAD ID,
Status,
Date

FROM
[C:\trend.xlsx]
(ooxml, embedded labels, table is Sheet1);

Calendar:
LOAD

While
MinDate + IterNo() -1 <=MaxDate;

LOAD
min(Date) as MinDate,
floor(today()) as MaxDate

Resident Trend;

Re: Weekend counts

smilingjohn — Thu, 16 Jul 2020 17:49:27 GMT

@Kushal_Chawda you are just awesome sir,

Now this is generating the the dates till today .

I tried with my actaul forlmula as shown belwo which is not giving me correct result when i add status ? Can you please correct this ?

sum(aggr(if(count({<Status={'Open'},Weekend>}ID)=0, above(total column(1)),count({<Status={'Open'},Weekend>}ID)),Weekend))*avg(1)

Re: Weekend counts

smilingjohn — Thu, 16 Jul 2020 19:06:59 GMT

Hi Kush,

The result works perfectly with your suggested expression which is this

sum(aggr(if(count({<Weekend>}ID)=0,above(total column(1)),count({<Weekend>}ID)),Weekend))*avg(1)

But when I ad my filter condition within the expression it dont work properly . Can u please correct me the below expression .

sum(aggr(if(count({<Status={'Open'},Weekend>}ID)=0, above(total column(1)),count({<Status={'Open'},Weekend>}ID)),Weekend))*avg(1)

And also when i make some list box selection the result dont act accoding to selections .

Without making any selection it displays proper result as hown in the below screenshot

And when i seelct 'aa' from the Dept list box it displays the count only to that perticular dates , rather it should behave like above .

Thanks

Re: Weekend counts

Kushal_Chawda — Thu, 16 Jul 2020 22:46:17 GMT

try below expression. If does not work then share your sample app

=sum(aggr(sum({<Date>}aggr({<Date>} if(count({<Date>}ID)>0,count({<Status={'Open'},Date>}ID), above(total column(1))),(Date, (NUMERIC, ASCENDING)))),Date))* avg(1)

Re: Weekend counts

smilingjohn — Fri, 17 Jul 2020 04:15:16 GMT

HI Kush,

The last expression you suggested is not working , Please find the attachment of the application.

Thanks

Re: Weekend counts

Kushal_Chawda — Fri, 17 Jul 2020 14:07:30 GMT

Without selection of Dept expression works fine, but if you also want to work that for other dimensions then you need to fill the Dates in your Date for each dimension

You may need to update the highlighted code if you want to add more dimensions other than Dept

Data:
LOAD
ID,
Status,
"Date",
AutoNumberHash128("Date"&Dept) as Key,
Dept
FROM [lib://Data/trend.xlsx]
(ooxml, embedded labels, table is Sheet1);

Calendar:
LOAD

While
MinDate + IterNo() -1 <=MaxDate;

LOAD
min(Date) as MinDate,
Floor(Today()) as MaxDate
//max(Date) as MaxDate

Resident Data;

Dates_Dept_T1:
Load Date
Resident Calendar;

Left Join(Dates_Dept_T1) // Add here other dimensions for which you want it to work
Load Dept
Resident Data;

Dates_Dept_T2:
Load *, AutoNumberHash128("Date"&Dept) as Key2
Resident Dates_Dept_T1;

Drop Table Dates_Dept_T1;

Concatenate(Data)
Load *
Resident Dates_Dept_T2
where not Exists(Key,Key2);

Drop Table Dates_Dept_T2;

Below expression then just works for me with or without selection of Dept

sum({<Weekend>}aggr({<Weekend>} if(count({<Weekend>}ID)>0,count({<Status={'Open'},Weekend>}ID), above(total column(1))),(Weekend, (NUMERIC, ASCENDING))))* avg(1)