topic Re: How to loop 6 months in pivot table based on application in QlikView

How to loop 6 months in pivot table based on application

Anonymous — Fri, 12 Feb 2016 05:39:05 GMT

Hi all

i am using pivot table for getting the no.of users for each application

i have nearly 35 application with 3 particulars for each application i have to show no.of users for last 6 months

some thing like

application name particulars 12/2/2016 11/2/2016 10/2/2016 ..............

no.of users 10 15 6

Wisdom total no.of users 100 120 40

training requests 0 1 5

30 applications

so i taken application name and particulars as dimension and

expression if(application='Wisdom' and Particular='no.of users' ,count({<Date={"$(vToday)"}>}userid)) for today(3 if statements for each application 1.no.of users,2.total no.of users ,3. training requests)

$(vYesturday) for yesterday and so on

$(vPrevious2) for day before yesturday

writing this expression(total 180 expressions) for 6 months is very difficult so is their any way to loop it for 6 months

and also how to sort applications based on user selection(a to z or z to a)

please help me

regards,

mahesh

Re: How to loop 6 months in pivot table based on application

marcus_sommer — Fri, 12 Feb 2016 09:30:37 GMT

It's not quite clear to me what do you want to do. If you used a date-dimension - maybe restricted with: if(date >= today() -180, date) - you could simply use a single expression with a count() and without set analysis.

- Marcus

Re: How to loop 6 months in pivot table based on application

Gysbert_Wassenaar — Fri, 12 Feb 2016 17:00:51 GMT

Like Marcus_Sommer I don't see why you can't use dimensions and just three expressions. Can you explain the problem with that?

Re: How to loop 6 months in pivot table based on application

Anonymous — Tue, 16 Feb 2016 13:20:19 GMT

Hi Marcus

i am attaching qvw and xl which i need like that

i tried with conditional available in expression tab still it is showing like attached

plz help me

regards,

Mahesh

Re: How to loop 6 months in pivot table based on application

marcus_sommer — Tue, 16 Feb 2016 14:28:03 GMT

For me it's not clear what should be the result - qvw and xls are identical.

- Marcus

Re: How to loop 6 months in pivot table based on application

Anonymous — Wed, 17 Feb 2016 03:08:53 GMT

In qvw for Application IPR it is showing both ipr count and bgcg count and also for Application BGCG it is showing both ipr count and bgcg count i want to have ipr count only for application ipr and bgcg count only for application bgcg

in the above xl for bgcg s.no is 2

regards,

mahesh

Re: How to loop 6 months in pivot table based on application

Anonymous — Wed, 17 Feb 2016 03:10:15 GMT

hi wassenaar thanks for your response

please go through the above attachments

Re: How to loop 6 months in pivot table based on application

jagan — Wed, 17 Feb 2016 03:15:49 GMT

Hi,

Not sure what you are trying to do? When I check your data model there is join/relation between Application and other tables, there should be some relation. Can you attach your raw data and your expected output, so that it is easier to understand and help you.

Regards,

jagan.

Re: How to loop 6 months in pivot table based on application

Anonymous — Wed, 17 Feb 2016 04:07:06 GMT

Hi ,

thanks for your reply i attached the qvw and expected output in excel please go through that

in the above xl for bgcg s.no is 2

Re: How to loop 6 months in pivot table based on application

marcus_sommer — Thu, 18 Feb 2016 07:44:02 GMT

Then you need only one expression with an appropriate condition like:

If(Application = 'IPR', count(IPR_ID), Count(BGCG_ID))

Re: How to loop 6 months in pivot table based on application

Anonymous — Tue, 01 Mar 2016 06:20:24 GMT

Hi Marcus Thanks for your reply

while finding total no.of users till date in the below example total no.of prp users

till 10/1/2016 the total count is 22 and on 10/1/2016 4 users are added so count is 26

like that total no.of users till date is increasing day by day how to get total users till date

i attached the file please go through that

Thanks&Regards,

Mahesh

Re: How to loop 6 months in pivot table based on application

marcus_sommer — Tue, 01 Mar 2016 15:23:09 GMT

I think these approach of a datamodel isn't suitable. Your main-dimensions to which you want to count some measures are not associated/linked with the other tables - it's a loosen table and there shouldn't be calculations between them and other tables.

Therefore I suggest to associate these tables and maybe create a canonical date for your different date-fields: Canonical Date.

- Marcus