Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a fields ID, Type, Days and I need to write expression which shows the count of ID where
days Less than 100 Days, days Less than 2 year, days greater than 100 Days
can anyone suggest me how can I calculate this.
Thanks.
Hi,
In this case you may use a pivot table with an calculated dimension (button "Create Calculated Dimension").
It shall be something like:
If(
Days>0 and Days<100,
'0 to 100 days',
If(
Days>=100 and Days<200,
'100 to 200 days',
//if( //other segments comes here
// ...
//)
)
)
Then the expression should be something like count(DISTINCT ID).
Regards
I have to show the table as in attached.
here class and state are under column Type
I have below data in days sample data
199
1
31
23
21
1161
1147
1146
1133
1132
1131
1128
1118
1008
986
981
971
958
949
939
937
935
931
930
909
883
881
879
874
869
861
858
851
845
839
837
827
826
825
823
820
819
816
813
811
809
806
804
797
796
792
791
783
782
778
774
770
769
767
764
760
756
755
753
750
749
746
743
741
736
735
732
729
727
726
725
724
722
719
718
715
714
712
711
708
707
706
701
700
699
697
693
691
690
687
686
685
684
683
680
679
678
526
676
669
666
665
663
662
657
656
649
645
644
641
637
630
629
628
624
623
622
620
617
448
615
614
610
609
607
601
600
599
596
594
592
589
588
587
586
582
581
580
579
578
575
574
573
572
571
569
567
565
564
561
560
559
558
557
553
551
550
547
546
545
543
531
525
523
519
518
517
516
515
512
511
510
5
509
508
505
504
503
502
500
499
498
497
496
495
494
491
490
489
488
487
486
484
483
482
481
480
477
476
475
479
473
470
469
456
458
460
462
463
465
467
449
461
453
454
450
447
434
432
431
429
428
440
438
437
435
433
430
427
426
425
423
421
420
419
418
417
414
270
768
713
524
474
459
413
412
411
410
406
393
392
391
389
386
385
384
383
379
378
377
376
375
372
371
370
369
368
365
please can anyone help me!!
Hi,
In this case you may use a pivot table with an calculated dimension (button "Create Calculated Dimension").
It shall be something like:
If(
Days>0 and Days<100,
'0 to 100 days',
If(
Days>=100 and Days<200,
'100 to 200 days',
//if( //other segments comes here
// ...
//)
)
)
Then the expression should be something like count(DISTINCT ID).
Regards
If statement as Fernando mentioned should suffice. You could also try using class function. For more buckets you may want to try interval match....
i think interval match is the better choice for you
M_Groups:
LOAD * INLINE [
Group, Start, End, Order
< 25, -10000, 24, 1
25-44, 25, 44, 2
45-54, 45, 54, 3
55-64, 55, 64, 4
65-80, 65, 79, 5
Other > 80, 80, 9998, 6
N/A, 9999, 9999, 7
];
then
IntervalAgeMatch: | |
INTERVALMATCH (Data) LOAD Start, End resident M_Groups; |
and after you are able to use Group as a dimension without calculating anything in the chart
Thanks. How can I write the dimension condition as
I have to show the count of id when Type=Class
and count of id when Type= State
Please help
Count({<Type={'Class','State'}>}Distinct ID)